Implementing Functions in VBA

[GreenyMcDuff]

Hi guys,

I found this Function to convert a column letter to number:

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 am trying to then use the value in a range:

            With ActiveSheet
                .Range("A1:" & ConvertToLetter & LastRow).Select
                
            End With

I don't think I have implemented the function properly as the code is not working

I would be most grateful for your help

Thanks

Chris

 

[Ruddles]

You have to tell it what the column number is so it can convert it to a column letter.

With ActiveSheet
      .Range("A1:" & ConvertToLetter([I]column_number[/I]) & LastRow).Select
End With

Do you have a variable which contains your column number at this point in your program?

 

[RoryA]

The function can also be lot simpler and more robust:

Function ConvertToLetter(iCol As Integer) As String
   ConvertToLetter = Replace(Cells(1, iCol).Address(0, 0), "1", "")
End Function

for example.

 

[Ruddles]

 

[GreenyMcDuff]

I see, thanks guys that helps a lot - and yes I have a column counter in there.

I have another question if you don't mind taking a look please

I have the variable "Col_Dummy" defined as a range.

I want to be able to select from this cell to the last row in my data set.

The last row of data is defined as a long variable with name "LastRow"

So just to be clear I need the code to say

Range(Col_Dummy & ":" & Col_Dummy.Column & LastRow)

I hope that makes sense

Thanks again guys

Chris

 

[RoryA]

Range(Col_Dummy ,cells(LastRow, Col_Dummy.Column))

 

[Jonmo1]

There really is no need to convert column Number to Letter.
Especially in VBA...
You can use the Cells property, this allows you to use either column number or letter.

Cells(Row#, Column # or Letter)


Try

Range(Col_Dummy, Cells(LastRow, Col_Dummy.Column))

 

[GreenyMcDuff]

Ok Thanks guys,

Rory, for some reason when I use your code, instead of selecting the range I want. It is selecting the range specified by LastRow and LastCol fom the below code:

Sub Import()
Dim LastRow As Long, LastCol As Long
Dim Col_Dummy As Range
    Application.Workbooks.Open ("H:\VBA Macros\Barnwell, Robin\Sales_Core_Report_Update.xls")
 
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(20, Columns.Count).End(xlToLeft).Column
            ActiveSheet.Name = "Data"
 
                Array1 = Array("Branch Code", "Branch Name", "Class Code", "Class Type", "Sum of fund under management in EUR", "% AUM", "Fund Name", "CLIENT SUBTOTAL", "FUND SUBTOTAL", "%", "% TOTAL")
                Array2 = Array(B, C, D, E, F, G, H, I, J, K, L)
                    For a = 0 To UBound(Array1)
                        Set Col_Dummy = Range("A1:IV10").Find(Array1(a))
                            Range(Col_Dummy, Cells(LastRow, Col_Dummy.Column)).Select
                                Selection.Copy
 
                                With Workbook.Sales_Core_Report_Template.xls
                                    Range(Array2(a) & "6").Paste
                                End With
                    Next
 
End Sub

Oh btw I'm using Excel 2003

 

[RoryA]

That code shouldn't even compile or run, so I don't see how it would select anything!
This line:

With Workbook.Sales_Core_Report_Template.xls

makes no sense. It should presumably be:

With Workbooks("Sales_Core_Report_Template.xls")

but then you are missing a sheet to paste to, the Range object in the following line is not qualified with a sheet and the Range object does not have a Paste method, only the Sheet does. (Range has PasteSpecial)

 

[GreenyMcDuff]

Ok sorry :S

I haven't written the code to paste the selection yet.... I wanted to get the correct selection first.

Am I correct in saying that IF:

Col_Dummy is in cell A5
Last Row = 1000

Then this code:

Range(Col_Dummy, Cells(LastRow, Col_Dummy.Column)).Select

will select from Cell A5 to A1000?

Thanks Rory I really appreciate your help