VBA code stops working at 53rd column

[Euler271]

I have VBA code in Access that creates an Excel workbook and fills a worksheet based on values in Access tables. The code fills five columns per state. (I have a state table that lists all 51 states, including DC.) My code stops working with "Error 1004 Method of 'Range 'of object'_Worksheet' failed" message. It always stops working when trying to insert something in column "BA", for some reason. In other words, in works just fine for the first two alphabets (A->Z and AA->AZ) but mysteriously stops with the error message in column "BA".

Does anyone have a clue why this is happening? I really need this to work. Thanks.

 

[Legacy 143009]

I have VBA code in Access that creates an Excel workbook and fills a worksheet based on values in Access tables. The code fills five columns per state. (I have a state table that lists all 51 states, including DC.) My code stops working with "Error 1004 Method of 'Range 'of object'_Worksheet' failed" message. It always stops working when trying to insert something in column "BA", for some reason. In other words, in works just fine for the first two alphabets (A->Z and AA->AZ) but mysteriously stops with the error message in column "BA".

Does anyone have a clue why this is happening? I really need this to work. Thanks.

Would you please send the .

PasteSpecial

line?

 

[Euler271]

This is the line that fails for column BA:

xlWs.Range(ConvertToLetter(i + 4) & 7).FormulaR1C1 = "Per Visit"

There's nothing unusual about this line. The previous line, which always works, is:

xlWs.Range(ConvertToLetter(i + 3) & 7).FormulaR1C1 = "CMS Rate"


​I don't have any named ranges and I'm only using one worksheet. Thanks for responding.

 

[Legacy 143009]

This is the line that fails for column BA:

xlWs.Range(ConvertToLetter(i + 4) & 7).FormulaR1C1 = "Per Visit"

There's nothing unusual about this line. The previous line, which always works, is:

xlWs.Range(ConvertToLetter(i + 3) & 7).FormulaR1C1 = "CMS Rate"


​I don't have any named ranges and I'm only using one worksheet. Thanks for responding.

First, use

.Value = "Per Visit"

instead of

.FormulaR1C1 = "Per Visit"

Second, would you please send the custom

ConvertToLetter()

function?

 

[Euler271]

Changing "FormulaR1C1" to "Value" didn't help but you were right to ask about my ConvertToLetter function. That's where the problem is. Here's the code:

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

I got this code off some website but I just tested it and it doesn't work past the number 52. I'm going to try changing my code to reference "cells" and not bother with converting to column letters. In other words, I'll try using:

xlWs.Cells(i + 4) & 7).Value = "Per Visit"

Thank you so much for showing me where the problem lay.

 

[Legacy 143009]

Changing "FormulaR1C1" to "Value" didn't help but you were right to ask about my ConvertToLetter function. That's where the problem is. Here's the code:

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

I got this code off some website but I just tested it and it doesn't work past the number 52. I'm going to try changing my code to reference "cells" and not bother with converting to column letters. In other words, I'll try using:

xlWs.Cells(i + 4) & 7).Value = "Per Visit"

Thank you so much for showing me where the problem lay.

You look familiar with VBA code. You chould write your own codes always instead of ready-functions

 

[Norie]

If you use Cells instead of Range then you can use the column number and won't need the ConvertToLetter function.

xlWs.Cells(7, i+4).Value = "Per Visit"
xlWs.Cells(7, i+3).Value = "CMS Rate"

 

[Euler271]

Actually, I found a new function:

Function ConvertToLetter(ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
Dim s As String
n = ColumnNumber
Do
c = ((n - 1) Mod 26)
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
ConvertToLetter = s
End Function

that works with any column number. The reason I didn't want to use xlWs.Cells...value is that I wasn't sure how that would work with something like this:

xlWs.Range(ConvertToLetter(i + 4) & "8:" & ConvertToLetter(i + 4) & xlWs.Cells(xlWs.Rows.Count, 1).End(xlUp).row).FormulaR1C1 = "=RC[-1]*RC[-2]"


I'm sure it's probably easy but the new conversion function works perfectly.

Thanks to everyone who responded.

 

[Norie]

I'll post this anyway.

With xlWs
    .Range(.Cells(8, i+4), .Cells(.Cells(xlWs.Rows.Count, 1).End(xlUp).Row, i+4).FormulaR1C1 = "=RC[-1]*RC[-2]"
End With

 

[Euler271]

Thanks for the post. I tried your code but I'm getting a "Syntax Error" message. Is there a missing ")" somewhere?