How to make absolute cell ref in loop work and skipping over a column in loop?

Question

I ALMOST got my code working but there are still two things wrong with it (two major things anyway).

1) The absolute cell ref. is not working as it does in Excel. I want for example $A5 but instead of changing to A6 A7 etc., it stays A5 throughout the loop.

2) There is a third column that I need to skip over. I only need my loop to write to columns under VOL and CAP, not %UTIL. How can I tell my loop to skip over $UTIL?

Option Explicit
Dim myRange As Range

Function numberOfRows() As Integer
    Debug.Print ("Start test")

    ThisWorkbook.Worksheets("LCI").Range("A9").Select

    Set myRange = Range(Selection, Selection.End(xlDown))

    Debug.Print ("Rows: " & myRange.Rows.Count)

    numberOfRows = (myRange.Rows.Count)
End Function

Function numberOfColumns() As Integer
    Debug.Print ("Start test")

    ThisWorkbook.Worksheets("LCI").Range("B8").Select

    Set myRange = Range(Selection, Selection.End(xlToRight))

    Debug.Print ("Columns: " & myRange.Columns.Count)

    numberOfColumns = (myRange.Columns.Count)
End Function

Sub TieOut(ByVal numberOfRows As Integer, ByVal numberOfColumns As Integer)
    Dim i As Integer 'i is row
    Dim j As Integer 'j is column

    For i = 1 To numberOfRows  'Loop over rows
       For j = 1 + 2 To numberOfColumns  'Loop over columns
        ThisWorkbook.Worksheets("Loop").Select
            With ThisWorkbook.Worksheets("Loop")
                **.Cells(i + 3, j + 1).Value = "=INDEX('ZAINET DATA'!$A$1:$H$39038,MATCH(Loop!B$2&TEXT(Loop!$A4,""M/D/YYYY""),'ZAINET DATA'!$C$1:$C$39038,0),4)"
                .Cells(i + 3, j + 2).Value = "=INDEX('ZAINET DATA'!$A$1:$H$39038,MATCH(Loop!B$2&TEXT(Loop!$A4,""M/D/YYYY""),'ZAINET DATA'!$C$1:$C$39038,0),5)"**
            End With
        Next j
    Next i
End Sub

Sub Test()
    Dim x As Integer
    Dim y As Integer
    x = numberOfRows()
    y = numberOfColumns()

    Call TieOut(x, y)
End Sub

Answer 1

Where have you defined it? Is it part of a BAS module?

EDIT: Put Option Explicit as the first line of BAS module & compile (Debug menu -> Compile).
You will see that there are compilation errors.

Remove Dim myRange As Range from Macro1 & Macro2.
Put it at the top of the BAS module (after option explicit)

Note: If you have a variable defined as part of a SUB, other SUB/Functions won't be able to use it. For TieOut to use myRange, it has to be defined at a scope where it can be used by all SUBs.

Also, Macro1 should run first - which assigns the value to MyRange (i.e. Set MyRange = .....)
If Macro1 is not run, MyRange will hold no value & hence there will be runtime error when your code tries to read the property (MyRange.Rows.Count).

Please take some time to read about Scoping of variables.
A variable needs to hold some value, before you try to read from it.

Answer 2

This is a great example to learn what 'scope' is. You declare (or bring into existence) a variable like the range you're trying to make. It lives inside the macro (or sub procedure) that you made. However, when the sub procedure is finished, your variable no longer has a place to live and gets evicted (dropped out of your computer's memory).

Unfortunately the way your coded your macros will not work the way you are hoping they work. Your myRanges will die everytime they reach an End Sub.

Also when passing arguments (your byvals) to another sub procedure (in this case your TieOut) you must provide the right number of arguments. Your TieOut procedure currently requires two. You cannot pass one and then the other. The correct way would look something like this:

Call TieOut(myRange.Rows.Count, myRange.Columns.Count)

Also you are trying to call a procedure named TieOut2. Not sure if thats a typo, but getting procedure names right is important.

VBA is very powerful and worth learning in my opinion. You look like you are scratching the surface. I would definitely search for some VBA tutorials online. Focus on calling procedures, variable declaration, and scope and I guarantee you will be able to solve your problem :D