Defining a Dim and then using it in a formula within VBA

[BOB12345]

Hi,

I am relatively new to writing Macros but what I want to achieve is within the Excel sheet to search through the data headings, find the corresponding Heading and then select the cell below it.

This is the Macro that I have so far.

Sub OverdueDate()
'
' OverdueDate Macro
'
Sheets("ComplaintsData").Select

Dim DateReceived As Range
Set DateReceived = Range(Rows("1:1")).Select.Find("Date_Received").Offset(1,0).Activate




'If Range("F1").Value = "Date_Received" And Range("P1").Value = "" Then

Range("P1").Select
ActiveCell.FormulaR1C1 = "8_Weeks_Date"
Range("P2").Select
'ActiveCell.FormulaR1C1 = "=RC[-10]+56"
ActiveCell.Formula = Range("DateReceived") + 56
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P13947")
Range("P2:P13947").Select
ActiveWindow.SmallScroll Down:=111

'Else: MsgBox "Make sure headings on ComplaintsData correspond with headings shown on Control Worksheet"
'End If

End Sub

Can anyone help?

Many Thanks

 

[Joe4]

A few things off the top of my head:

You don't use the "Select" and "Activate" statements is setting a range variable.
It should just be:

[I][B]Dim DateReceived As Range[/B]
[I][B]Set DateReceived = Rows("1:1").Find("Date_Received").Offset(1,0)[/B][/I][/I]

Second, since "DateReceived" is already a range variable, you don't use the double-quotes around it or the word "Range" when referencing it.
So this:

[I]Range("P2").Select
[I][I][B]ActiveCell.Formula = Range("DateReceived") + 56[/B][/I][/I][/I]

Can simply be re-written like this:

Range("P2").Formula = DateReceived.Value + 56

(no reason to use "Select" and "ActiveCell", that slows the code down - most anytime you have two consecutive rows like that, they can be combined like shown above - you do not need to select a range to do something to it).

 

[BOB12345]

That is brilliant thank you.

I do have a question is there a way of using the above as a cell reference i.e. if DataReceived = F2 to Input in Cell P2 effectively F2 +56. Using DateReceived.Value the results aren't as expected.

 

[Joe4]

Try this:

    Range("P2").Formula = "=" & DateReceived.Address(0, 0) & " + 56"

 

[BOB12345]

That is brilliant thank you worked a treat.

1 last question in my above formula I have Selection.AutoFill Destination:=Range("P2:P13947") How can I autofill by having the end cell dynamic as the range and rows of data will be different all the time.

Thank you so much for your help.

 

[Joe4]

1 last question in my above formula I have Selection.AutoFill Destination:=Range("P2:P13947") How can I autofill by having the end cell dynamic as the range and rows of data will be different all the time.

First thing is to identify a column that you can use to determine where the data ends (so pick a column that always has data in it). Let's say it is column O, for this example.
Then find the last row like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"O").End(xlUp).Row

Then, we can just substitute that variable is for our ending row number, i.e.

Range("P2:P" & lastRow)