Macro Help - Validation List to summary tab

[PghYoPro]

Hello,

I had a similar problem a while back but I can't seem to find out why the macro is not working.

• I have a sheet titled "Income Statement (% of TNPR)" with a validation dropdown in cell B2.
• The validation dropdown data comes from cells B2:298 on a sheet titled "Data Validation".
• I want the macro to cycle through the data validation list in cell B2 on "Income Statement (% of TNPR)" and return the results of cell E32 to an existing blank tab "Results" starting in cell A1. I also want the correlating dropdown name in cell B2 on "Income Statement (% of TNPR) to be placed starting in cell B1 on the "Results" tab

Here is the macro I am attempting to run:


Sub TNPR()
Dim i As Long
With ActiveSheet
For i = 2 To 298
.Range("B2").Value = Sheets("Data Validation").Range("B" & i).Value
ActiveSheet.Calculate
Sheets("results").Range("A" & i - 3).Value = .Range("E32").Value
ActiveSheet.Calculate
Sheets("results").Range("B" & i - 3).Value = .Range("B2").Value
Next i
End With
End Sub

 

[hotabae]

Hello,

• I have a sheet titled "Income Statement (% of TNPR)" with a validation dropdown in cell B2.
• The validation dropdown data comes from cells B2:298 on a sheet titled "Data Validation".
• I want the macro to cycle through the data validation list in cell B2 on "Income Statement (% of TNPR)" and return the results of cell E32 to an existing blank tab "Results" starting in cell A1. I also want the correlating dropdown name in cell B2 on "Income Statement (% of TNPR) to be placed starting in cell B1 on the "Results" tab

Sub TNPR()
Dim i As Long
With ActiveSheet
For i = 2 To 298
.Range("B2").Value = Sheets("Data Validation").Range("B" & i).Value
ActiveSheet.Calculate
Sheets("results").Range("A" & i - 3).Value = .Range("E32").Value
ActiveSheet.Calculate
Sheets("results").Range("B" & i - 3).Value = .Range("B2").Value
Next i
End With
End Sub

Well... If nothing Else, the first 2 iterations of your 'For' loop would cause issues I think.

@ i = 2 :

Range("A" & i - 3) -> Range("A-1") when in fact, I think you are looking for it to be -> Range("A1")

Try using (1's instead of 3's)

ActiveSheet.Calculate
Sheets("results").Range("A" & i - 1).Value = .Range("E32").Value
ActiveSheet.Calculate
Sheets("results").Range("B" & i - 1).Value = .Range("B2").Value

Hope that helps

 

[PghYoPro]

Solved. Didn't check my ranges!