InputBox to insert the last column filled.

[JODomingos]

Hi Guys.

I have a macro which is based on replace multiple cells in a specific range. This range is variable, because i need to input the last column filled with data. So whenever i need to run, i need to get in visual basic codes and change the code line. Ih there a way to create an input box that i could insert the last column instead of get inside the visual basic modules to change it?

Follows below the code: The bold "EGQ" word is the variable column which i must to filled before run it.

Sub ReplaceWithAverage()
Dim Cell As Range
For Each Cell In Range("C4", Cells(Rows.Count, "EGQ").End(xlUp)).SpecialCells(xlConstants)
Cell.Replace "NN", Cell.End(xlDown).Value, xlWhole, , , , False, False
Next
End Sub

Thanks in advance

 

[JoeMo]

Try this. Note that the quote marks around EGQ have been removed.

Sub ReplaceWithAverage()
Dim Cell As Range, EGQ As String
EGQ = InputBox("Enter the column letter(s) for the last filled column")
If EGQ = "" Then Exit Sub  'user clicked Cancel
For Each Cell In Range("C4", Cells(Rows.Count, EGQ).End(xlUp)).SpecialCells(xlConstants)
    Cell.Replace "NN", Cell.End(xlDown).Value, xlWhole, , , , False, False
Next
End Sub

 

[JODomingos]

Try this. Note that the quote marks around EGQ have been removed.

Sub ReplaceWithAverage()
Dim Cell As Range, EGQ As String
EGQ = InputBox("Enter the column letter(s) for the last filled column")
If EGQ = "" Then Exit Sub  'user clicked Cancel
For Each Cell In Range("C4", Cells(Rows.Count, EGQ).End(xlUp)).SpecialCells(xlConstants)
    Cell.Replace "NN", Cell.End(xlDown).Value, xlWhole, , , , False, False
Next
End Sub

It worked Joemo. Thank you so much. I really a appreciate your support.

 

[JoeMo]

It worked Joemo. Thank you so much. I really a appreciate your support.

You are welcome - thanks for the reply.