I asked for help earlier and have had some help which gets me most of the way there. The problem is its very different coding to what I have ever previously used so I cant make the last few tweaks to debug it.
Can someone help out please...
The original problem was:
I need the macro to:
1. Take todays date from F3 (of current "report" sheet)
2. go to "tables" sheet
3. Scan column B to find the most recent date to todays date,
4. take values from cell D and E (from the row selected in step 3) and put them both into cell P45 of the "report" sheet. ("xxx" & "yyy" into one cell)
5. Go back to the "tables" sheet (and to the same row as found in step 3), copy value of Cell "AP" into Cell P46 of the "report" sheet
6. return to "tables" sheet, move up 1 row from step 3
7. similar to step 4 except paste into O45 (of "report" sheet)
8. similar to step 5 except paste into O46 (of "report" sheet)
9. Repeat steps 6,7,8 another 3 times, each time pasting values into the previous column of the "report" sheet
The proposed solution was:
Sub DoReport()
Dim wsRpt As Worksheet
Dim wsTbls As Worksheet
Dim rngFnd As Range
Dim dt
Dim I As Long
Set wsRpt = Worksheets("Report")
Set wsTbls = Worksheets("Tables")
dt = wsRpt.Range("F3")
Do
Set rngFnd = wsTbls.Range("B:B").Find(dt)
If rngFnd Is Nothing Then dt = dt - 1
Loop Until Not rngFnd Is Nothing
If Not rngFnd Is Nothing Then
For I = 1 To 4
wsRpt.Range("P45").Offset(, 1 - I) = rngFnd.Offset(1 - I, 2) & rngFnd.Offset(1 - I, 3)
wsRpt.Range("P46").Offset(, 1 - I) = rngFnd.Offset(1 - I, 40)
Next I
End If
End Sub
I have tried it and it certainly is along the right lines. The first problem though, is that this code keeps picking the earliest date (at the top of the list) rather than the most recent date. So I think there is a problem with the line:
Set rngFnd = wsTbls.Range("B:B").Find(dt)
Does this compare the value in a cell in column B with value "dt" and then if it matches, set rngfnd to equal that cell location?
(not cell value, cell location)It needs to be location as later in the code we start offsetting from rngfnd
Please help, I need to try to get this sorted asap.
cheers.
JRS
Can someone help out please...
The original problem was:
I need the macro to:
1. Take todays date from F3 (of current "report" sheet)
2. go to "tables" sheet
3. Scan column B to find the most recent date to todays date,
4. take values from cell D and E (from the row selected in step 3) and put them both into cell P45 of the "report" sheet. ("xxx" & "yyy" into one cell)
5. Go back to the "tables" sheet (and to the same row as found in step 3), copy value of Cell "AP" into Cell P46 of the "report" sheet
6. return to "tables" sheet, move up 1 row from step 3
7. similar to step 4 except paste into O45 (of "report" sheet)
8. similar to step 5 except paste into O46 (of "report" sheet)
9. Repeat steps 6,7,8 another 3 times, each time pasting values into the previous column of the "report" sheet
The proposed solution was:
Sub DoReport()
Dim wsRpt As Worksheet
Dim wsTbls As Worksheet
Dim rngFnd As Range
Dim dt
Dim I As Long
Set wsRpt = Worksheets("Report")
Set wsTbls = Worksheets("Tables")
dt = wsRpt.Range("F3")
Do
Set rngFnd = wsTbls.Range("B:B").Find(dt)
If rngFnd Is Nothing Then dt = dt - 1
Loop Until Not rngFnd Is Nothing
If Not rngFnd Is Nothing Then
For I = 1 To 4
wsRpt.Range("P45").Offset(, 1 - I) = rngFnd.Offset(1 - I, 2) & rngFnd.Offset(1 - I, 3)
wsRpt.Range("P46").Offset(, 1 - I) = rngFnd.Offset(1 - I, 40)
Next I
End If
End Sub
I have tried it and it certainly is along the right lines. The first problem though, is that this code keeps picking the earliest date (at the top of the list) rather than the most recent date. So I think there is a problem with the line:
Set rngFnd = wsTbls.Range("B:B").Find(dt)
Does this compare the value in a cell in column B with value "dt" and then if it matches, set rngfnd to equal that cell location?
(not cell value, cell location)It needs to be location as later in the code we start offsetting from rngfnd
Please help, I need to try to get this sorted asap.
cheers.
JRS
