New to VBA. More than one input to run the sub-CodePudding

I have a sub which require me to input one serial number at a time to run the program. However, I want to select a range of cells in excel, for example, "A1:A15" as my inputs. So that I don't have to run the program by entering the serial number one by one, which is time-consuming. Is there any way to do that?

The sub2 requires the input of serial number as string.

CodePudding user response：

You can use a For Each loop for that.

As an example, I've written here a small macro which enters the value "1" in each cell of the selection:

Sub test()
Dim r As Range
For Each r In Selection:
  r.Value = 1
Next
End Sub

In a similar way, you can use the same technique to run over your input range:

Sub test(input as Range)
Dim r As Range
For Each r In input:
  ' here you describe what to do with cell r
Next
End Sub

CodePudding user response：

So you have a Sub which does something with a single value, in your case a serial number. Let's assume the name of the Sub is DoSth. You need to add a parameter to this sub in this case the serial number.

Sub doSth (ByVal serialNumber as Variant)    ' Adjust the datatype to your needs
   ' Do sth with the serialNumber
   debug.print serialNumber
End Sub

In another sub you loop over the range like already shown in another post

Sub loopRange

   dim rg as range
   set rg = Range("A1:A15")

   dim sngCell as range
   for each sngCell in rg
      doSth sngcell.value
   next sngCell
   
end Sub