marjieboone77
New Member
- Joined
- Dec 28, 2016
- Messages
- 3
Hi there - I'm working on a macro for copying and pasting a row of data based on a lookup of the selected row by reference in a different worksheet. Specifically, I want the rows that have formulas but no values to remain as formulas; however, this setup is only converting row 3 to values no matter what the lookup reference value is (due to the Range (B3:B3) reference. Help!!!
Sub Worksheet_Calculate()
Dim A As Range, r As Range, B As Range, s As Range
Set A = Range("B3:3")
Set B = Range("A:A")
For Each s In B
If s.Value = Worksheets("Air Emissions Grid").Range("N3") Then
For Each r In A
If r.HasFormula And r > 0 Then
r.Value = r.Value
End If
Next r
End If
Next s
End Sub
Thanks,
Marjie
Sub Worksheet_Calculate()
Dim A As Range, r As Range, B As Range, s As Range
Set A = Range("B3:3")
Set B = Range("A:A")
For Each s In B
If s.Value = Worksheets("Air Emissions Grid").Range("N3") Then
For Each r In A
If r.HasFormula And r > 0 Then
r.Value = r.Value
End If
Next r
End If
Next s
End Sub
Thanks,
Marjie