Help coding a tricky macro

[JRS]

Hi people,

Usually I record my macro's but this one requires what I would usually get a Vlookup funtion to do in a cell and is quite a bit more complicated then anything I've tried befor.

I would just ask for what I need one step at a time and try to put it together myself, but just incase there is anyone out there who can do this sort of thing in 2 minutes flat, I've written out the whole psuedo code and put it below as it could potentially save me several days of getting angry with my computer.

Even if you know how parts of it would work then I would appreciate any help I can get.


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

Any help would be really appreciated as this is waaay past what I am capable of.

Thanks
JRS

 

[Norie]

JRS

Are the dates in column B on the tables sheet in any sort of order?

Also by most recent do you mean the closest date not after the date in F3?

 

[Norie]

I've had a go and think I might have got somewhere near the basic idea.

I got a bit confused by the last bit and couldn't quite work out what you wanted to repeat and how many times you wanted it to repeat.

Also, still unsure about the date thing so just relied on the date being looked for being present in the list.

So here it is and I'm pretty sure it won't do what you want exactly, but it might give you some ideas.

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

 

[JRS]

Hi Norie,

I'll take a look and see how far I can get things.

The dates in column B are in order (oldest date at the top, future dates at the bottom)

I want it to look for the most recent date that is in the past, and todays date is what is in F3. The date being looked for wont necessarily be in the list, this is why I want it to find the most recent (past) date.

I can probably figure out the repeats myself but need the help going up 1 row from the previous date that was used, and then copying certain values from that row into the other worksheet.

The bit where it searches for the most recent date is a really tricky bit so let me know if you can help with that.

Cheers
JRS