Range Method to Set Cell Formulas with LastRow

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance for anyone who is assisting. I will test any recommendations and will let you know the outcome.

Item:

I've found the last row in my data ("LastRow"), and now I'm trying to set a formula in cells A5 to "LastRow" to a formula, but I'm getting errors.

The First issue seems to be with the use of "A5:A"&"LastRow" in the code and then I'm also having issues with the formula as I've tested trying to use it by just setting "A5:A"&"LastRow" to "A5:A6".

Once again thanks in advance.

Code:
ActiveSheet.Range("A5:A" & "LastRow").Formula = "=IF( OR(B5 = "", B4 = "Rem."), "Delete", IF( AND( N1 = "", LEFT( B1, 4) = "Date"), "Total", IF( AND( N1 <> "", LEFT( B1, 4) = "Date"), N1, IF(AND(B5 <> "", A4 = "delete"), B2, A4 ))))"
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You need to remove the quotes from LastRow and at the same time you need to double-up on the quotes inside the formula.
 
Upvote 0
2 things
LastRow is a variable and should not be in quotes.
You also to double up on the quotes in the formula, otherwise VBA will see the " as the end of the string:

Code:
ActiveSheet.Range("A5:A" & LastRow).Formula = "=IF(OR(B5 ="""",B4=""Rem.""),""Delete"",IF(AND(N1="""",LEFT(B1,4)=""Date""),""Total"",IF(AND(N1<>"""",LEFT(B1,4)=""Date""),N1,IF(AND(B5<> """",A4=""delete""),B2,A4))))"
 
Upvote 0
That worked!

Thanks so much to both of you - "Fluff" and "Scott Huish", for fixing my issue and the quick response!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top