Determining Last Cell Used and Inserting Formula in Different Column

Question

Trying to get a vba code that determines the last row used in column A then inserting the following formulas in the stated columns until the last row.

Range("AC2:AC" & lastrow).FormulaR1C1 = "=IF($S2="""",""Discard"",$T2-8)"
Range("AD2:AD" & lastrow).FormulaR1C1 = "=IF($S2="""",""Discard"",($S2*1000000000000)/($AC2*10000000))"

Hoping to do this without referencing the worksheet name. Thanks for any help!

Edit: What I am trying to figure out is the last row used in Column A. Then have the above formulas inserted in columns AC and AD from row 2 to the determined last row. I hope this clarifies what I am asking.

[This is a very helpful answer](https://stackoverflow.com/a/11169920/5936117). The rest of your question is too vague. — MoondogsMaDawg, Jan 03 '18 at 18:11

score 0 · Answer 1 · answered Jan 03 '18 at 18:10

0

You can use the rows.count property of the sheet, which will give you the maximum row used (in any column) then go up from that in Column A, some pseudo code below

lastRow = range("A" & sheet.rows.count).end(xlup).row

answered Jan 03 '18 at 18:10

Jpad Solutions

332
1
12

This only answer partially the question. – Jan 03 '18 at 18:55
it does? I believe that the formula part works, the OP was just having problems identifying the last row, which does seem to be clarified in his edit – Jpad Solutions Jan 04 '18 at 08:32
Jpad-When I tried using your code above I encountered an error. Can't recall what it was at this time. Thanks for the help though! – pmgibs Jan 05 '18 at 16:04

score 0 · Accepted Answer · answered Jan 05 '18 at 16:02

0

Identified last row using

Dim lastrow As Long
lastrow = Range("A1").End(xlDown).Row

Then modified my formulas to this:

Range("AC2:AC" & lastrow).Formula = "=IF($S2="""",""Discard"",$T2-8)"

And it works perfectly!

answered Jan 05 '18 at 16:02

pmgibs

37
1
9

Determining Last Cell Used and Inserting Formula in Different Column

2 Answers2