Hello all,
I have this VBA code that pulls info from Sheet "TestIP" and fills it into sheet "Input", based off input from sheet "Test".
The code works, which I'm fairly happy, and thankful about (I'm new to VBA, and I got a lot of help from reading this board). My problem is, the code is not very expandable. Does anyone know a shorter way to write this, possible using variables? There is a possibility this sheet/VBA code will be used for 100+ terminals vice the 13 test one's I'm using now. I appreciate any help you can offer!
Thanks!
I have this VBA code that pulls info from Sheet "TestIP" and fills it into sheet "Input", based off input from sheet "Test".
Code:
Sub Run_IP()
Dim Terminal As String
Dim Cache As String
Terminal = (Cells(9, 7).Value)
If Terminal = "103" Then
Cache = Range("TestIP!B3").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C3").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D3").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E3").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F3").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G3").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H3").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "104" Then
Cache = Range("TestIP!B4").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C4").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D4").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E4").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F4").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G4").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H4").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "105" Then
Cache = Range("TestIP!B5").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C5").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D5").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E5").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F5").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G5").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H5").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "106" Then
Cache = Range("TestIP!B6").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C6").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D6").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E6").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F6").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G6").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H6").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "107" Then
Cache = Range("TestIP!B7").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C7").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D7").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E7").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F7").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G7").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H7").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "108" Then
Cache = Range("TestIP!B8").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C8").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D8").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E8").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F8").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G8").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H8").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "109" Then
Cache = Range("TestIP!B9").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C9").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D9").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E9").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F9").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G9").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H9").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "110" Then
Cache = Range("TestIP!B10").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C10").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D10").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E10").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F10").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G10").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H10").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "111" Then
Cache = Range("TestIP!B11").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C11").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D11").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E11").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F11").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G11").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H11").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "112" Then
Cache = Range("TestIP!B12").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C12").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D12").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E12").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F12").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G12").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H12").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "113" Then
Cache = Range("TestIP!B13").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C13").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D13").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E13").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F13").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G13").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H13").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "114" Then
Cache = Range("TestIP!B14").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C14").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D14").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E14").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F14").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G14").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H14").Value
Range("Input!B8").Value = Cache
ElseIf Terminal = "115" Then
Cache = Range("TestIP!B15").Value
Range("Input!B2").Value = Cache
Cache = Range("TestIP!C15").Value
Range("Input!B3").Value = Cache
Cache = Range("TestIP!D15").Value
Range("Input!B4").Value = Cache
Cache = Range("TestIP!E15").Value
Range("Input!B5").Value = Cache
Cache = Range("TestIP!F15").Value
Range("Input!B6").Value = Cache
Cache = Range("TestIP!G15").Value
Range("Input!B7").Value = Cache
Cache = Range("TestIP!H15").Value
Range("Input!B8").Value = Cache
Else
MsgBox Prompt:=Terminal & " is not a valid option, please try again!"
End If
Thanks!