Im confused! Can anyone "un"-confuse me?

  • Thread starter Thread starter Dave Potter
  • Start date Start date
D

Dave Potter

Im sorry this post is so long. I'm trying to include as much info as
possible so you can tell whats going on.
Here's the scenario:
I have a workbook with two tabs (Main & Data). The main sheet is where
the user will enter data and the data sheet is where the code will
dump the data. There is a combo box on the Main sheet from which the
user will select the current date. Now when the user the selects the
date and then enters the data on the page, the user will then click a
button (called Save Data) on the main page at which point the data
will be transfered to the Data sheet. Then when a new date is selected
from the combo box the sheet is reset to blank cells. Here is the
question. If you look at the code below the top section was written by
someone else and it all works fine. about 2/3 down you will notice a
comment section where I state that I added the rest of the lines. The
lines I added to the code will work to a point. When you click the
Save Data button the data is transfered to the Data page. BUT...
When you select a new date from the combo box the data referenced by
the code I wrote does not reset. It carries over to any date you
select. I am totally confused why my code (which I simply copied and
pasted from the previous code and changed cell references) does not
work correctly. Again, I apologize fro the length of the post.

BTW OfficeXP XL2002 WinXP pro

Thank you for reading all this.
Dave



Worksheets("Data").Range("B" & FoundOnRow) = Range("N3").Value '
Store
Worksheets("Data").Range("C" & FoundOnRow) = Range("K3").Value
' Preparer
Worksheets("Data").Range("D" & FoundOnRow) = Range("H9").Value
' GSFR
Worksheets("Data").Range("E" & FoundOnRow) = Range("H10").Value
' GSDTR
Worksheets("Data").Range("F" & FoundOnRow) = Range("H12").Value
' ST
Worksheets("Data").Range("G" & FoundOnRow) = Range("M10").Value
' OSBLC
Worksheets("Data").Range("H" & FoundOnRow) = Range("M11").Value
' OSBC
Worksheets("Data").Range("I" & FoundOnRow) = Range("M12").Value
' OSB1
Worksheets("Data").Range("J" & FoundOnRow) = Range("M13").Value
' OSB5
Worksheets("Data").Range("K" & FoundOnRow) = Range("M14").Value
' OSBOB
Worksheets("Data").Range("L" & FoundOnRow) = Range("M15").Value
' OSBQ
Worksheets("Data").Range("M" & FoundOnRow) = Range("M16").Value
' OSBD
Worksheets("Data").Range("N" & FoundOnRow) = Range("M17").Value
' OSBN
Worksheets("Data").Range("O" & FoundOnRow) = Range("M18").Value
' OSBP
Worksheets("Data").Range("P" & FoundOnRow) = Range("M19").Value
' OSBOC
Worksheets("Data").Range("Q" & FoundOnRow) = Range("M23").Value
' CSBLC
Worksheets("Data").Range("R" & FoundOnRow) = Range("M24").Value
' CSBC
Worksheets("Data").Range("S" & FoundOnRow) = Range("M25").Value
' CSB1
Worksheets("Data").Range("T" & FoundOnRow) = Range("M26").Value
' CSB5
Worksheets("Data").Range("U" & FoundOnRow) = Range("M27").Value
' CSBOB
Worksheets("Data").Range("V" & FoundOnRow) = Range("M28").Value
' CSBQ
Worksheets("Data").Range("W" & FoundOnRow) = Range("M29").Value
' CSBD
Worksheets("Data").Range("X" & FoundOnRow) = Range("M30").Value
' CSBN
Worksheets("Data").Range("Y" & FoundOnRow) = Range("M31").Value
' CSBP
Worksheets("Data").Range("Z" & FoundOnRow) = Range("M32").Value
' CSBOC
' I added these lines of code but when i save data
' and go to a different date the numbers remain on
' the new page
Worksheets("Data").Range("AA" & FoundOnRow) = Range("D20").Value
' BDBAG1
Worksheets("Data").Range("AB" & FoundOnRow) = Range("D21").Value
' BDBAG2
Worksheets("Data").Range("AC" & FoundOnRow) = Range("D22").Value
' BDBAG3
Worksheets("Data").Range("AD" & FoundOnRow) = Range("D23").Value
' CREDIT
Worksheets("Data").Range("AE" & FoundOnRow) = Range("E20").Value
' BDINI1
Worksheets("Data").Range("AF" & FoundOnRow) = Range("E21").Value
' BDINI2
Worksheets("Data").Range("AG" & FoundOnRow) = Range("E22").Value
' BDINI3
Worksheets("Data").Range("AH" & FoundOnRow) = Range("E23").Value
' CREDITINI
Worksheets("Data").Range("AI" & FoundOnRow) = Range("F20").Value
' BDAMT1
Worksheets("Data").Range("AJ" & FoundOnRow) = Range("F21").Value
' BDAMT2
Worksheets("Data").Range("AK" & FoundOnRow) = Range("F22").Value
' BDAMT3
Worksheets("Data").Range("AL" & FoundOnRow) = Range("F23").Value
' CREDITAMT
End Sub
 
Sorry to do this, but unless you are able to figure out the answer
from the code i posted (in which case I believe you are clairvouant)
I think Idetermined that the problem is not in the code i wrote, but
in a different macro in the module. SO, I am including the entire
module. If this is inappropriate to do in a newsgroup, please let me
know since i am kinda new around here.

Dave

the module (GetSaveData):

Option Explicit
Public FoundOnRow As Long
'******************************************************************
' Purpose of the following is simply to replace the values on the
' Main worksheet with those found in the Data table for the
' date that was entered in the Drop Down
'******************************************************************
Sub GetData()
' Insure that the Main worksheet is active
Worksheets("Main").Activate
' Call the sub to get the Row number associated with the selected
date
GetTheRowNumber ' FoundOnRow
' Perform a series of VLookups to populate the sheet with the
results
Range("K3").Value = Worksheets("Data").Range("C" & FoundOnRow)
Range("H9").Value = Worksheets("Data").Range("D" & FoundOnRow)
' GSFR
Range("H10").Value = Worksheets("Data").Range("E" & FoundOnRow)
' GSDTR
Range("H12").Value = Worksheets("Data").Range("F" & FoundOnRow)
' ST
' Range M10 to M19 is the Opening store bank.
' The values here should be pre-populated from the Closing store
' bank from the previous day.
'Need to check first that we're not on the first day of the year
' as we'll get an error (since there isn't a line above)
If FoundOnRow = 7 Then
' In this case only, we'd have to open up the worksheet for
the
' previous year and extract the values from the last day.
' For the time being, we'll just use this annoying message box
MsgBox "First day of the year"
Else
Range("M10").Value = Worksheets("Data").Range("Q" & FoundOnRow
- 1) ' CSBLC (yesterday)
Range("M11").Value = Worksheets("Data").Range("R" & FoundOnRow
- 1) ' CSBC
Range("M12").Value = Worksheets("Data").Range("S" & FoundOnRow
- 1) ' CSB1
Range("M13").Value = Worksheets("Data").Range("T" & FoundOnRow
- 1) ' CSB5
Range("M14").Value = Worksheets("Data").Range("U" & FoundOnRow
- 1) ' CSOB
Range("M15").Value = Worksheets("Data").Range("V" & FoundOnRow
- 1) ' CSBQ
Range("M16").Value = Worksheets("Data").Range("W" & FoundOnRow
- 1) ' CSBD
Range("M17").Value = Worksheets("Data").Range("X" & FoundOnRow
- 1) ' CSBN
Range("M18").Value = Worksheets("Data").Range("Y" & FoundOnRow
- 1) ' CSBP
Range("M19").Value = Worksheets("Data").Range("Z" & FoundOnRow
- 1) ' CSBOC
End If
Range("M23").Value = Worksheets("Data").Range("Q" & FoundOnRow)
' CSBLC
Range("M24").Value = Worksheets("Data").Range("R" & FoundOnRow)
' CSBC
Range("M25").Value = Worksheets("Data").Range("S" & FoundOnRow)
' CSB1
Range("M26").Value = Worksheets("Data").Range("T" & FoundOnRow)
' CSB5
Range("M27").Value = Worksheets("Data").Range("U" & FoundOnRow)
' CSBOB
Range("M28").Value = Worksheets("Data").Range("V" & FoundOnRow)
' CSBQ
Range("M29").Value = Worksheets("Data").Range("W" & FoundOnRow)
' CSBD
Range("M30").Value = Worksheets("Data").Range("X" & FoundOnRow)
' CSBN
Range("M31").Value = Worksheets("Data").Range("Y" & FoundOnRow)
' CSBP
Range("M32").Value = Worksheets("Data").Range("Z" & FoundOnRow)
' CSBOC
End Sub
'******************************************************************
' Purpose of the following is simply to replace the values on the
' data worksheet with those entered in the Main table for the
' date that was entered in the Drop Down
'******************************************************************
Sub SaveData()
' Insure that the Main worksheet is active
Worksheets("Main").Activate
' Call the sub to get the Row number associated with the selected
date
GetTheRowNumber
' Before allowing the user to save data, this is where we would
put
' in some data validation. For the time being, let's just make
sure
' that PreparedBy is filled in.
If Range("K3").Value = "" Then
MsgBox "Please fill-in your name as the Preparer"
Exit Sub
End If
' Replace the values in the selected row with the values entered
' in the main worksheet.
' Note that although we're storing the Opening Store bank, the
data isn't
' used to populate the sheets. It's probably unnecessary to keep
it if
' the Opening Store Bank will *always* the previous days Closing
Store Bank
' Actually, it may not be a bad idea to keep is since if the user
changes a
' value on the OSB you can check it against the previous days CSB
for
' any discrepancies. We'll get back to this later.
Worksheets("Data").Range("B" & FoundOnRow) = Range("N3").Value
' Store
Worksheets("Data").Range("C" & FoundOnRow) = Range("K3").Value
' Preparer
Worksheets("Data").Range("D" & FoundOnRow) = Range("H9").Value
' GSFR
Worksheets("Data").Range("E" & FoundOnRow) = Range("H10").Value
' GSDTR
Worksheets("Data").Range("F" & FoundOnRow) = Range("H12").Value
' ST
Worksheets("Data").Range("G" & FoundOnRow) = Range("M10").Value
' OSBLC
Worksheets("Data").Range("H" & FoundOnRow) = Range("M11").Value
' OSBC
Worksheets("Data").Range("I" & FoundOnRow) = Range("M12").Value
' OSB1
Worksheets("Data").Range("J" & FoundOnRow) = Range("M13").Value
' OSB5
Worksheets("Data").Range("K" & FoundOnRow) = Range("M14").Value
' OSBOB
Worksheets("Data").Range("L" & FoundOnRow) = Range("M15").Value
' OSBQ
Worksheets("Data").Range("M" & FoundOnRow) = Range("M16").Value
' OSBD
Worksheets("Data").Range("N" & FoundOnRow) = Range("M17").Value
' OSBN
Worksheets("Data").Range("O" & FoundOnRow) = Range("M18").Value
' OSBP
Worksheets("Data").Range("P" & FoundOnRow) = Range("M19").Value
' OSBOC
Worksheets("Data").Range("Q" & FoundOnRow) = Range("M23").Value
' CSBLC
Worksheets("Data").Range("R" & FoundOnRow) = Range("M24").Value
' CSBC
Worksheets("Data").Range("S" & FoundOnRow) = Range("M25").Value
' CSB1
Worksheets("Data").Range("T" & FoundOnRow) = Range("M26").Value
' CSB5
Worksheets("Data").Range("U" & FoundOnRow) = Range("M27").Value
' CSBOB
Worksheets("Data").Range("V" & FoundOnRow) = Range("M28").Value
' CSBQ
Worksheets("Data").Range("W" & FoundOnRow) = Range("M29").Value
' CSBD
Worksheets("Data").Range("X" & FoundOnRow) = Range("M30").Value
' CSBN
Worksheets("Data").Range("Y" & FoundOnRow) = Range("M31").Value
' CSBP
Worksheets("Data").Range("Z" & FoundOnRow) = Range("M32").Value
' CSBOC
' I added these lines of code but when i save data
' and go to a different date the numbers remain on
' the new page
Worksheets("Data").Range("AA" & FoundOnRow) = Range("D20").Value
' BDBAG1
Worksheets("Data").Range("AB" & FoundOnRow) = Range("D21").Value
' BDBAG2
Worksheets("Data").Range("AC" & FoundOnRow) = Range("D22").Value
' BDBAG3
Worksheets("Data").Range("AD" & FoundOnRow) = Range("D23").Value
' CREDIT
Worksheets("Data").Range("AE" & FoundOnRow) = Range("E20").Value
' BDINI1
Worksheets("Data").Range("AF" & FoundOnRow) = Range("E21").Value
' BDINI2
Worksheets("Data").Range("AG" & FoundOnRow) = Range("E22").Value
' BDINI3
Worksheets("Data").Range("AH" & FoundOnRow) = Range("E23").Value
' CREDITINI
Worksheets("Data").Range("AI" & FoundOnRow) = Range("F20").Value
' BDAMT1
Worksheets("Data").Range("AJ" & FoundOnRow) = Range("F21").Value
' BDAMT2
Worksheets("Data").Range("AK" & FoundOnRow) = Range("F22").Value
' BDAMT3
Worksheets("Data").Range("AL" & FoundOnRow) = Range("F23").Value
' CREDITAMT
End Sub
Sub GetTheRowNumber()
' Find out the row for this entry.
' This is going to involve working with dates
' Dates in Excel suck and they hate me as much as I hate them.
' There's a better way to do this. I'll fix it later but this works
for now
' Make sure the Main sheet is active
Worksheets("Main").Activate
' Set some variables for Year, Month and Day
Dim SelYear As Integer
Dim SelMonth As Integer
Dim Selday As Integer
' Set the Year, Month and Day variables to the derived Year, Month
and Day
' taken from Main!N4.
' Note that the drop down on the Main sheet is linked to N4 so
that
' whenever the drop down changes, N4 will reflect the selected
date
SelYear = Year(Range("N4"))
SelMonth = Month(Range("N4"))
Selday = Day(Range("N4"))
' Do a match to find the position of the date selected in the Data
table
FoundOnRow = Application.Match(CLng(DateSerial(SelYear, SelMonth,
Selday)), _
Sheets("Data").Columns("A:A"), 0)
End Sub


Im sorry this post is so long. I'm trying to include as much info as
possible so you can tell whats going on.
Here's the scenario:
I have a workbook with two tabs (Main & Data). The main sheet is where
the user will enter data and the data sheet is where the code will
dump the data. There is a combo box on the Main sheet from which the
user will select the current date. Now when the user the selects the
date and then enters the data on the page, the user will then click a
button (called Save Data) on the main page at which point the data
will be transfered to the Data sheet. Then when a new date is selected
from the combo box the sheet is reset to blank cells. Here is the
question. If you look at the code below the top section was written by
someone else and it all works fine. about 2/3 down you will notice a
comment section where I state that I added the rest of the lines. The
lines I added to the code will work to a point. When you click the
Save Data button the data is transfered to the Data page. BUT...
When you select a new date from the combo box the data referenced by
the code I wrote does not reset. It carries over to any date you
select. I am totally confused why my code (which I simply copied and
pasted from the previous code and changed cell references) does not
work correctly. Again, I apologize fro the length of the post.

BTW OfficeXP XL2002 WinXP pro

Thank you for reading all this.
Dave
Click to expand...
snip
 
Back
Top