lundi 24 juillet 2017

VBA Excel: Non-Active Sheet and Unknown Number of Rows. If cell contains value, perfom calculation and insert new value into cell on same row

I have tried my best to search for the answer but can't get what I'm looking for. I'm very new to VBA so may be going wrong in several places here . . .

I'm creating a data formatter that processes data with a different numbers of records (rows) each time it is used. Data will be on non-active sheet. First row has headings. I've successfully used similar code to the code below to identify rows with certain data on it and clear the contents of other cells on that row.

The reason I refer to column E is because it is the only column that has data in every record. I then have to find the rows that have a value in column BU, then multiply that value by 20 and insert the result in column BX of the same row.

I keep getting Run-time Error 13 but don't understand as it's simply a number with 2 decimal places in cell BU, and currently there is nothing in BX.

Sub CalcTotalLTA()

Dim i As Variant

'counts the no. of rows in E and loops through all
For i = 1 To Sheets("Input").Range("E2", Sheets("Input").Range("E2").End(xlDown)).Rows.Count

'Identifies rows where columns BU has a value
If Sheets("Input").Cells(i, 73).Value <> "" Then

'calculate Total LTA

Sheets("Input").Cells(i, 76).Value = Sheets("Input").Cells(i, 73).Value * 20

End If

Next i

End Sub

Aucun commentaire:

Enregistrer un commentaire