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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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,351
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,956
Members
431,689
Latest member
jacker01

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