VBA Code too long.

zsgray86

New Member
Joined
Jan 13, 2012
Messages
8
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".

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
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Welcome to the board..

For starters, each section
Code:
    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

Could be reduced like:

Code:
Range("Input!B2:B8").Value = Application.Transpose(Range("TestIP!B3:H3"))
 
Upvote 0
Now, is your post an accurate representation of what's going on?

It seems the only difference between each possible value of Terminal, is the row# used for the TestIP range...

And, those row#s seem to correspond to the last 2 characters of Terminal

Terminal = 103 : it uses Row3
Terminal = 104 : it uses Row4
Terminal = 111 : it uses Row11


Is this an accurate and consistent pattern?
 
Upvote 0
Awesome. Truly. Wish I had known that one earlier. And yes, that is the pattern I have going, and plan to continue (I thought it'd make things easier). Thank you very much for the help!
 
Upvote 0
If it would make things easier still, I could move the rows down to match the Terminal number (Terminal 103 = Row 103, etc.). Would that help?
 
Upvote 0
Something like this maybe

Code:
Sub Run_IP()
 
    Dim Terminal As String
    Dim Cache As String
    Dim lRow As Long
 
    Terminal = (Cells(9, 7).Value)
    
    Select Case Terminal
        Case "103": lRow = 3
        Case "104": lRow = 4
        Case "105": lRow = 5
        Case "106": lRow = 6
        Case "107": lRow = 7
        Case "108": lRow = 8
        Case "109": lRow = 9
        Case "110": lRow = 10
        Case "111": lRow = 11
        Case "112": lRow = 12
        Case "113": lRow = 13
        Case "114": lRow = 14
        Case "115": lRow = 15
        Case Else: MsgBox Prompt:=Terminal & " is not a valid option, please try again!"
                    Exit Sub
    End Select
    Range("Input!B2:B8").Value = Application.Transpose(Range("TestIP!B" & lRow & ":H" & lRow))
 
End Sub
 
Upvote 0
Glad to help..

OK, given the pattern described in my last post, then you should be able to replace all those If/ElseIf's with

Code:
Dim MyRow As Long
MyRow = Terminal - 100
Range("Input!B2:B8").Value = Application.Transpose(Range("TestIP!B" & MyRow).Resize(1,7))
 
Upvote 0
Glad to help, thanks for the feedback...


Now FYI, this is a bit nitpicky...but..

This is technically not wrong
Range("TestIP!B3")

But it is preferred to write it like
Sheets("TestIP").Range("B3")


You can save yourself alot of frustration by writing things this way.

For example, if you have several lines to write that refer to the Sheet TestIP
You have to write the Sheet name over and over again.

Range("TestIP!B3").Value = "Hello"
Range("TestIP!B3").Interior.ColorIndex = 3

But, doing it this way you can write the sheet name only once.

Code:
With Sheets("TestIP")
    .Value = "Hello"
    .Interior.ColorIndex = 3
End With


Hope that helps.
 
Upvote 0
That will be something that I'm sure will come in handy not too long from now. Thanks for the tidbit!
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top