Dynamic range to fix.

[Prasad Vithanala]

Hi I want to use dynamic range for the following vba code. The below code says. only particular row number. but I need code according to the data available in the cells. My code is working fine with below. But I need generic code with without range. Am writing code for EoMonth function

Sub EoMonth()

Sheets("RPI").Select
Range("B1").EntireColumn.Insert

Dim Cell As Range

For Each Cell In Range("A2:A434")
Cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(Cell, -1)

Next Cell

Range("B2:B434").Font.Color = vbRed
Range("B2:B434").Interior.ColorIndex = 6

Sheets("CGT").Select
Range("B1").EntireColumn.Insert
For Each Cell In Range("A2:A102")
Cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(Cell, -1)
Next Cell

Range("B2:B102").Font.Color = vbRed
Range("B2:B102").Interior.ColorIndex = 6

End Sub

 

[offthelip]

Try this : I have used a variant array rather than looping through every cells in the range because using a variant array much much faster which could be important if you have a lot of rows:

Sheets("RPI").Select
Range("B1").EntireColumn.Insert
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
'Dim Cell As Range
dates = Range(Cells(2, 1), Cells(lastrow, 1)) ' load column A into a variant array
Oedates = Range(Cells(2, 2), Cells(lastrow, 2))
'For Each Cell In Range("A2:A434")
For i = 1 To UBound(dates)
'Cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(Cell, -1)
Oedates(i, 1) = Application.WorksheetFunction.EoMonth(dates(i, 1), -1)
Next i
Range(Cells(2, 2), Cells(lastrow, 2)) = Oedates
Range(Cells(2, 2), Cells(lastrow, 2)).Font.Color = vbRed
Range(Cells(2, 2), Cells(lastrow, 2)).Interior.ColorIndex = 6

Notethe code for the second sheet would be the same

 

[Prasad Vithanala]

Hi, I used above code by modifying as below, am getting error as: "Compile Error" - expected array, for Ubound, how to resolve it ?

Sub EoMonth()
Dim lastrow As Long
Dim dates As String
Dim oedates As String
Dim i As String

Sheets("RPI").Select
Range("B1").EntireColumn.Insert
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
'Dim Cell As Range
dates = Range(Cells(2, 1), Cells(lastrow, 1)) ' load column A into a variant array
oedates = Range(Cells(2, 2), Cells(lastrow, 2))
'For Each Cell In Range("A2:A434")
For i = 1 To UBound(dates)
'Cell.Offset(0, 1) = Application.WorksheetFunction.EoMonth(Cell, -1)
oedates(i, 1) = Application.WorksheetFunction.EoMonth(dates(i, 1), -1)
Next i
Range(Cells(2, 2), Cells(lastrow, 2)) = oedates
Range(Cells(2, 2), Cells(lastrow, 2)).Font.Color = vbRed
Range(Cells(2, 2), Cells(lastrow, 2)).Interior.ColorIndex = 6

End Sub

 

[offthelip]

you have defined two variables as string that should be variant arrays:

Dim dates As String
Dim oedates As String

change to:

Dim dates As variant
Dim oedates As variant

You don't actually need to define them because vba defaults to defining them as variant, I don't usually define them because any variable that is loaded from the workhseet of written to the workhseet needs to be a variant ( I did mention I was using variant arrays)
Also I should be defined as long, NOT a string it is a indes through a loop!!

Dim i As Long

 

[Prasad Vithanala]

you have defined two variables as string that should be variant arrays:

Dim dates As String
Dim oedates As String

change to:

Dim dates As variant
Dim oedates As variant

You don't actually need to define them because vba defaults to defining them as variant, I don't usually define them because any variable that is loaded from the workhseet of written to the workhseet needs to be a variant ( I did mention I was using variant arrays)
Also I should be defined as long, NOT a string it is a indes through a loop!!

Dim i As Long

Wow! awesome, this code was worked now. Thanks for the help. It is very appriciable

 

[smozgur]

Wow! awesome, this code was worked now. Thanks for the help. It is very appriciable

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.