Column Issue with Range

[jerry8989]

Thanks in advance for any help.

I'm doing some importing of excel data from one file to another. That works fine. My problem is that when I get the new data in I have to do some stuff to the data to clean it up. I first declare the variables below and set them

Public iLastCol As Integer
Public iLastRow As Integer

iLastCol = Me.Parent.Worksheets("VData").UsedRange.Columns.Count
iLastRow = Me.Parent.Worksheets("VData").UsedRange.Rows.Count

This tells me the last column and row with data. I now have to select the column after iLastCol because I have to put a formula into every row in that column. Currently iLastCol = 18 which lands on letter R.

I don't know how to set the Range proptery to allow to select column iLastCol + 1 down to row iLastRow?


Prior to me switching to using column numbers the VBA code looked like this

Worksheets("Sheet1").Range("S2:S" & iLastRow).FormulaR1C1 = "=IF(ISERROR(SEARCH(R1C,RC[-17],1)) = FALSE, ""X"","""")"

Thank You for your help

[royUK]

jerry8989

Read the Forum Rules & use Code tags in future.

I'm not sure why you are making the variables as Public and I would use Long insttead of Integer, in case the variables exceed the Integer's capacity.

Dim rLastCol As Long
Dim rLastRow As Long

rLastCol = Me.Parent.Worksheets("VData").UsedRange.Columns.Count + 1
rLastRow = Me.Parent.Worksheets("VData").UsedRange.Rows.Count

Worksheets("Sheet1").Range(Cells(1, rLastCol), Cells(rLastCol), Cells(rLastRow, _
    rLastCol)).FormulaR1C1 = "=IF(ISERROR(SEARCH(R1C,RC[-17],1)) = FALSE, ""X"","""