Excel VBA to What If Test

[nirvehex]

Hello,

I'm trying to write some VBA that works as follows:

On my 'Summary' tab I have, in D2, a vlookup formula which generates a number. This in turn generates a value in K14 on the 'Summary' tab.

On my 'Input' tab I have a list of values in H17:H36.

What I want to do is have some VBA that does some what if testing. I'm guessing it would run a for loop. But I'm trying to have it write the values in H17 through H36 one at a time into D2 on my summary tab and record the value that is generated K14 on the summary tab onto the input tab in I17:I36. At the end I would like the macro to reinsert the original formula back into D2 on the summary tab.

So for example:

The first run of the macro would copy the value in H17 on the input tab, paste it in D2 on the summary tab, and record the resulting number, which is always in K14, into cell I17 on the input tab. It would repeat this process until it runs down through H36 and then rewrite the original formula back in cell D2 on the summary tab.

Any idea how to write this code?

Thanks.

[Bob Phillips]

What are the formulae in D2 and K14 on Summary?

[YasserKhalil]

Hi nirvehex
Try this
Replace my formula
=RANDBETWEEN(E2,F2) with yours >>

Sub Test()

Dim strFormula As String
Dim cell As Range

strFormula = "=RANDBETWEEN(E2,F2)"
For Each cell In Sheets("Input").Range("H17:H36")
Sheets("Summary").Range("D2").Value = cell.Value
cell.Offset(, 1).Value = Sheets("Summary").Range("K14").Value
Next cell
Sheets("Summary").Range("D2").Value = strFormula

End Sub

[nirvehex]

YasserKhalil - I'm not sure I understand.

[SamT]

The VBAX Grand Master (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_faq_user_ranks) already asked you for the only important information.

Nobody can help you without that information.

We will give you another 7 days to reply again

[nirvehex]

Sorry! I overlooked that. My fault.

D2: =VLOOKUP(Input!D25,Input!F17:G36,2,FALSE)
K14: =(((SUMIF(Details!BF2:BF23,"Yes",Details!AQ2:AQ23)+SUMIF(Details!BF2:BF23,"Yes",Details!AT2:AT23)+SUMIF(Details!BF2:BF23,"Yes",Details!AX2:AX23))*12)+SUMIF(Details!BF2:BF23,"Yes",Details!BA2:BA23))*(1-B2)

[nirvehex]

Here is a macro recorded VBA code that does exactly what I need it to do. I'm just looking to automate this with some sort of looping.



Sub test()
'
' test Macro
'


'
Sheets("Input").Select
Range("H17").Select
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I17").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab


Range("H18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I18").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I19").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I20").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H21").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I21").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H22").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I22").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I23").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I24").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H25").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I25").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I26").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H27").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I27").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H28").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I28").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I29").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I30").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H31").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I31").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H32").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I32").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H33").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I33").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I34").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I35").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

'Repeat and move down one in H column on Input tab and down one on I column on Input tab

Range("H36").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("D2").Select
ActiveSheet.Paste
Range("K14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Input").Select
Range("I36").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

End Sub


Thanks!

[nirvehex]

Anyone? :hi:

[Bob Phillips]

Sub test()
Dim cell As Range

With Worksheets("Input")

For Each cell In .Range("H17:H36")

cell.Copy Worksheets("Summary").Range("D2")
.Range("K14").Copy
cell.Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Next cell
End With
End Sub

[nirvehex]

Thanks xld. Didn't quite work for some reason, but my code albeit repetitive and sloppy doesn't take long to run afterall.

[Bob Phillips]

Didn't work in what way?

[nirvehex]

It didn't seem to take into account the I column into account and pasted blank cells.

If you look at my code it's a loop coded out manually:

Select H17 on Input tab; it increases by 1 each time
Copy H column
Select D2 on Summary tab; this always remains D2
Paste into D2 on Summary tab; this always remains D2
Select K14 on summary tab; this always remains k14
Copy K14 on Summary tab; this always remains K14
Select I17 on Input tab; this increases by 1 each time
Paste into I17 on input tab; this increase by 1 each time


and repeat with increases in cells stated above....until H and I get to 36.

Thanks for the help!