Issue with VBA .offset number

Question

I have been searching for an hour or so and I can't seem to find anything on this issue. I may be wording it wrong so I'm not getting the results im looking for. I am having an issue with .offset (, 99). It seems that any time I put a like digit in there such as 99, 88, 11 or whatever combo there is, it raises runtime error 13 type mismatch.

But when I change it to 98 or any other non like combo it works fine. Is there some way that I need to program it if it has the same number multiple times?

Sub Scope()

    Dim ws As Excel.Worksheet

    Set ws = ThisWorkbook.Sheets("Future Ongoing Vetting") ' change to name of your sheet

    Dim x As Long
        x = 2

    Do Until ws.Cells(x, 7) = ""

        With ws.Cells(x, 5)

            .Value = "• Customer name: " & .Offset(, 29) & _
            Chr(10) & "• Customer Bus Org: " & .Offset(, 30) & _
            Chr(10) & "• Internal Circuit ID: " & .Offset(, 2) & _
            Chr(10) & "• Customer prem address: " & .Offset(, 12) & " " & .Offset(, 13) & " " & .Offset(, 14) & ", " & .Offset(, 15) & ", " & .Offset(, 16) & ", " & .Offset(, 17) & _
            Chr(10) & "• Customer demarc: " & .Offset(, 18) & " " & .Offset(, 20) & ", " & .Offset(, 19) & " " & .Offset(, 21) & _
            Chr(10) & "• MRR: " & .Offset(, 68) & _
            Chr(10) & "• Current Off Net MRC: $" & .Offset(, 10) & _
            Chr(10) & "• Margin Percent: " & .Offset(, 89) & _
            Chr(10) & "• Bandwidth: " & .Offset(, 6) & " ( " & .Offset(, 7) & "Mb )" & _
            Chr(10) & "• Customer term end date: ""TEXT(.Offset(, 32),""mmm-dd-yyyy"")" & _
            Chr(10) & "• New Vendor: " & .Offset(, 106) & _
            Chr(10) & "• New MRC: $" & .Offset(, 102) & _
            Chr(10) & "• New NRC: $" & .Offset(, 103) & _
            Chr(10) & "• New Install Interval: " & .Offset(, 105) & _
            Chr(10) & "• New Term: " & .Offset(, 104) & _
            Chr(10) & Chr(10) & "Planner Notes: This project is replacing the existing " & .Offset(, 6) & " ( " & .Offset(, 7) & "Mb ) based solution from ( " & .Offset(, 5) & " ) with a new " & .Offset(, 99) '& " ( " & .Offset(, 100) & "Mb ) based solution from ( " & .Offset(, 106) & " )."
    '        Chr(10) & "RFA # " & .Offset(, 107) & " install notes: " & _
    '        Chr(10) & "Please install ( " & .Offset(, 31) & " ) Ethernet " & .Offset(, 99) & " ( " & .Offset(, 100) & "Mb  ) circuit with ( " & .Offset(, 106) & " ) from ( " & .Offset(, 101) & " )  to ( [Customer Prem] " & .Offset(, 12) & " " & .Offset(, 13) & " " & .Offset(, 14) & ", " & .Offset(, 15) & ", " & .Offset(, 17) & " )."
    '        Chr(10) & "This new circuit will be used to replace existing customer circuit ECCKT: ""&RC[1]&"", ""&RC[109]&"", ""&RC[110]&"", ICCKT: "" & RC[2] & ""." & _
    '        Chr(10) & "The customer prem address is ( ""&RC[12]&"" ""&RC[13]&"" ""&RC[14]&"", ""&RC[15]&"", ""&RC[16]&"", ""&RC[17]&"" ) and customer demarc is ( ""&RC[18]&"" ""&RC[20]&"", ""&RC[19]&"" ""&RC[21]&"" )."



        End With

        x = x + 1
    Loop

End Sub

There is something else going on there. If you post some code we might be able to spot it. — user6432984
– user6432984, Commented Oct 17, 2016 at 2:47
Don't put code in a comment. Edit it into your question. Also, your problem likely has little to do with offset per se. — John Coleman
– John Coleman, Commented Oct 17, 2016 at 2:53
The starting range is very important. Chance are that Offset is trying to extend your range beyond the boundaries of the worksheet. — user6432984
– user6432984, Commented Oct 17, 2016 at 2:54
"it seems to error out" isn't very informative. What is the actual error? — John Coleman
– John Coleman, Commented Oct 17, 2016 at 2:56

John Coleman · Accepted Answer · 2016-10-17 03:11:01Z

Your code is written in a way that makes it very difficult to debug. I would recommend replacing that extremely long statement by:

Dim s As String 'at the top of the module, and then in the loop ...

s  = "• Customer name: " & .Offset(, 29)
s = s & Chr(10) & "• Customer Bus Org: " & .Offset(, 30)
s = s & Chr(10) & "• Internal Circuit ID: " & .Offset(, 2)
s = s & Chr(10) & "• Customer prem address: " & .Offset(, 12) & " " & .Offset(, 13) & " " & .Offset(, 14) & ", " & .Offset(, 15) & ", " & .Offset(, 16) & ", " & .Offset(, 17)
s = s & Chr(10) & "• Customer demarc: " & .Offset(, 18) & " " & .Offset(, 20) & ", " & .Offset(, 19) & " " & .Offset(, 21)
s = s & Chr(10) & "• MRR: " & .Offset(, 68)
s = s & Chr(10) & "• Current Off Net MRC: $" & .Offset(, 10)
s = s & Chr(10) & "• Margin Percent: " & .Offset(, 89)
s = s & Chr(10) & "• Bandwidth: " & .Offset(, 6) & " ( " & .Offset(, 7) & "Mb )"
s = s & Chr(10) & "• Customer term end date: ""TEXT(.Offset(, 32),""mmm-dd-yyyy"")"
s = s & Chr(10) & "• New Vendor: " & .Offset(, 106)
s = s & Chr(10) & "• New MRC: $" & .Offset(, 102)
s = s & Chr(10) & "• New NRC: $" & .Offset(, 103)
s = s & Chr(10) & "• New Install Interval: " & .Offset(, 105)
s = s & Chr(10) & "• New Term: " & .Offset(, 104)
s = s & Chr(10) & Chr(10) & "Planner Notes: This project is replacing the existing " & .Offset(, 6) & " ( " & .Offset(, 7) & "Mb ) based solution from ( " & .Offset(, 5) & " ) with a new " & .Offset(, 99) '& " ( " & .Offset(, 100) & "Mb ) based solution from ( " & .Offset(, 106) & " )."

.Value = s

Splitting the needlessly long statement into a series of shorter statements will enable you to better pinpoint where the type mismatch error is coming from.

score 1 · Accepted Answer · 2016-10-17 03:57:24Z

1

I would prefer calling a function to build the string. You'll be able isolate the code and debug it in the Immediate Window without.

Note: I use ws.Rows(5).Cells to set a reference the cells in the row. In this way; I can reference each column by it's actual column number not the offset of Cells(RowNumber, 5). It also allows me to use a shorthand method of referring to cells on that row. e.g. r(1) refers to column 1 r(10) refers to column 10 of Rows(RowNumber).

Function getDescription(ws As Worksheet, RowNumber As Long) As String
    Dim r As Range
    Dim Data(14)

    Set r = ws.Rows(5).Cells

    Data(0) = "• Customer name: " & r(34)
    Data(1) = "• Customer Bus Org: " & r(35)
    Data(2) = "• Internal Circuit ID: " & r(7)
    Data(3) = "• Customer prem address: " & r(17) & " " & r(18) & " " & r(19) & ", " & r(20) & ", " & r(21) & ", " & r(22)
    Data(4) = "• Customer term end date: " & Chr(34) & Format(r(37), "mmm-dd-yyyy") & Chr(34)
    Data(5) = "• Customer demarc: " & r(23) & " " & r(25) & ", " & r(24) & " " & r(26)
    Data(6) = "• MRR: " & r(73)
    Data(7) = "• Current Off Net MRC: $" & r(15)
    Data(8) = "• Margin Percent: " & r(94)
    Data(9) = "• Bandwidth: " & r(11) & " ( " & r(12) & "Mb )"
    Data(10) = "• New Vendor: " & r(111)
    Data(11) = "• New MRC: $" & r(107)
    Data(12) = "• New NRC: $" & r(108)
    Data(13) = "• New Install Interval: " & r(110)
    Data(14) = "• New Term: " & r(109)

    getDescription = Join(Data, Chr(10))

End Function

edited Oct 17, 2016 at 3:57

answered Oct 17, 2016 at 3:34

user6432984

3 Comments

John Coleman Over a year ago

Animated gif. Cool. However they do it, OP needs to modularize that long statement so that its parts can be individually debugged.

user6432984 Over a year ago

I love ScreenToGif and have been trying to get better at using it. Yes, agreed. I like this technique because it gets a lot of the ordinary fluff out of the way so that you can focus on the code line for line.

Justin German Over a year ago

I figured it out! It was something stupid... I forgot that I had formulas in row two and the formulas in columns 99 and 100 were errored out. In my mind i was thinking the code started on row 3... Ugh. Thank you all for your suggestions, it was a great help and i got some great ideas.

Collectives™ on Stack Overflow

Issue with VBA .offset number

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related