Help coding a tricky macro

JRS

New Member
Joined
Mar 10, 2011
Messages
44
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.:)
Code:
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
 
Last edited:

JRS

New Member
Joined
Mar 10, 2011
Messages
44
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top