Test-If not empty, prompt with InputBox for multiplier

Question

This is what I'm going for, but I'm pretty green when it comes to loops:

If (G1<>"", InputBox for a multiplier for range G2:G's LastRow,"")

Loop through column BZ1 (or preferably, the last column)

Or:

G1:BZ1 each contain the name of an order set. I need to manually enter the number of times each order set will be used. D2:D1001 are the number of times an item occurs in each set. I need to multiply D2:D1001 by the input box's number and enter that result for each item into G2:G1001.

I have multiple order sets and need to multiple each one by a different number of stores every time that this macro will be run. Order sets are in the columns, items are in the rows.

Answer 1

This should do the trick - just change that Sheet1 to whatever your sheet's name is.

So first we get the last column of data to use for our first loop (by getting the value of lastcol), then we start looping through the columns. We assign a value to multiplier through the InputBox, and after that we loop through every cell in the column and multiply it by the number you entered. Then we move on to the next column until we've run out of data.

I've updated the text in the InputBox to display the header text for each column each time.

Sub Test()

Dim sht As Worksheet, lastcol As Long, lastrow As Long, multiplier As Integer

Set sht = ThisWorkbook.Worksheets("Sheet1")
lastcol = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column

For i = 7 To lastcol
    If Cells(1, i) <> vbNullString Then
        multiplier = InputBox("Number of stores using set " & Cells(1, i) & ".")
        lastrow = sht.Cells(sht.Rows.Count, 4).End(xlUp).Row

        For j = 2 To lastrow
            On Error Resume Next
            Cells(j, i).Value = Cells(j, 4).Value * multiplier
            On Error GoTo 0
        Next j
    End If
Next i

End Sub