So I have made (and works) a simple macro on excel to simply open another excel file, and go to a selected cell and goto the end (in a right direction), to get the latest filled in cell (as this extends by 1 each month).
I needed to get 2 points, the end and whatever the end was - 73 to have a range, After I selected the latest cell, I got the column number (ie. A = 1, 6 = f etc...), and I then called a function parsing this to return the column letters again.
Whilst doing this I came across something that has confused me logically and can't figure out why this happens, so am hoping a guru out there can explain this to me.
So as you will see in the code, I have 2 Integers declared;
After getting to the cell I want, I make fin = to the ActiveCell Column. I then simply do ;
Start = fin - 73
But when I call the function, ConvertToLetter, I got the type mismatch error when I passed start through, so I forced it through like so;
ref1 = ConvertToLetter(CInt(start))
And it worked fine.
The full code;
Sub Test()
Dim rng As Range
Dim start, fin As Integer
Dim ref1, ref2 As String
Dim file, tabn As String
Dim XL As Object
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set rng = Range("C149")
file = "<File name>"
tabn = "<Tab Name>"
Set XL = CreateObject("Excel.Application")
XL.Application.Visible = True
XL.AskToUpdateLinks = False
XL.DisplayAlerts = False
XL.Application.Workbooks.Open (file)
Set wkb = XL.ActiveWorkbook
Set wks = wkb.Sheets(tabn)
wks.Activate
Set rng = wks.Range("C149")
rng.Select
rng.End(xlToRight).Select
fin = XL.ActiveCell.Column
start = fin - 73
If VarType(start) = vbInteger Then Debug.Print "Its an int!"
If VarType(start) = vbLong Then Debug.Print "Its a long!"
If VarType(start) = vbSingle Then Debug.Print "Its a single?"
If VarType(start) = vbDouble Then Debug.Print "ew double"
wkb.Close
XL.AskToUpdateLinks = True
XL.DisplayAlerts = True
XL.Quit
Set rng = Range("a1")
rng.Select
ref1 = ConvertToLetter(CInt(start))
ref2 = ConvertToLetter(fin)
End Sub
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
Please excuse the formatting, and the ugly if statements (that was just there to reassure myself that start is an int
What baffled me is that when it went through the if statements, it returned "Its an int!"
So why wasn't the function letting start be parsed through?
I managed to avoid the
ref1 = ConvertToLetter(CInt(start))
In general if I applied this before hand
Before:
start = fin - 73
After:
start = (fin - 73)
And I no longer have to parse it using CINT.
But could someone explain why it didn't think start was an int? And why encapsulating it in brackets, it does?
Cause im my head I have this logic;
If you have 2 int's, and apply any mathematical problem to them, the result will always be an int, for example; 10 / 3 = 3, not 3.33333333
So why was it in this case it wasn't an int?
Many Thanks.
In your declaration:
Dim start, fin As Integer
start is Variant, not int. Do:
Dim start As Integer, fin As Integer