I have managed to solve a problem whereby I got VBA to ignore the header row of my sheet and perform a calculation on the remaining data. Code below. However now that I have it working it misses the very last row, no matter how many rows of data I am working with. There is no reason in the data for this - the last row has values and the same type of values as the rows where the code worked.
Column E is the only column that has data in all cells. When I used "For i = 1 to ..." I received a Run-time Error 13, when I changed this to "For i = 2 to ..." the code worked.
Option Explicit
Sub CalcTotalLTA()
Dim i As Variant
Dim ws As Worksheet
Set ws = Worksheets("Input")
'counts the no. of rows in E and loops through all
For i = 2 To ws.Range("E2", ws.Range("E2").End(xlDown)).Rows.Count
'Identifies rows where columns BU has a value
If ws.Cells(i, 73).Value <> "" Then
'calculate Total LTA
ws.Cells(i, 76).NumberFormat = "0.00"
ws.Cells(i, 76).Value = ws.Cells(i, 73).Value * 20
End If
Next i
End Sub
Change For i = 2 To ws.Range("E2", ws.Range("E2").End(xlDown)).Rows.Count to For i = 2 To ws.Range("E2", ws.Range("E2").End(xlDown)).Rows.Count + 1.
Let's say you have 10 rows of data, located on rows 2-11. Your code says "From rows 2 to 10, do this logic." But you want to do it on rows 2-11.