Prev: I need VBA Help for Excel spreadsheet -- Please!
Next: ounces vs. pounds
From: Steve on 21 May 2010 14:42
I'm writing code to standardize a report I get from someone.

1. I need to change the single character value in each cell to the
actual name using Select Case
Example if cell value = B then "Bacon"
else if value = C then "Cheese"
else if value = E then "Eggs"
etc

I've gotten the following code using example found here but I'm stuck
on
defining my column range. My code works as long as there are no blank
cells.

and how do I use test each cell and stop at last cell of column even
if there is a blank row.

My code Start
Sub FixReport()
Dim ColNum As Long
Dim RngHeaders As Range
Dim RngColumn As Range
Dim ColHeader As String
'Set the rng of the column headers
Set RngHeaders = Range("A4", Cells(1,
Columns.Count).End(xlToLeft))
'Get the column header to search for
ColHeader = "ThisColumn"
'Find the column number of the column
ColNum = RngHeaders.Find(What:=ColHeader, LookAt:=xlWhole).Column
Set RngColumn = Range(Cells(5, ColNum), Cells(5,
ColNum).End(xlDown))
MsgBox RngColumn.Address ' For testing
End Sub
My code End

Thanks
Steve
From: Dave Peterson on 21 May 2010 14:54
Instead of looping through the cells, you could just do a series of
edit|replaces.

Record a macro when you...

Select the range
Edit|replace
What: B
with: Bacon
Make sure all the settings are ok (match entire cell contents, match case)
replace all

Continue recording for the other choices.

The edit|replace macro should be lots faster than the looping.

Steve wrote:
>
> I'm writing code to standardize a report I get from someone.
>
> 1. I need to change the single character value in each cell to the
> actual name using Select Case
> Example if cell value = B then "Bacon"
> else if value = C then "Cheese"
> else if value = E then "Eggs"
> etc
>
> I've gotten the following code using example found here but I'm stuck
> on
> defining my column range. My code works as long as there are no blank
> cells.
>
> and how do I use test each cell and stop at last cell of column even
> if there is a blank row.
>
> My code Start
> Sub FixReport()
> Dim ColNum As Long
> Dim RngHeaders As Range
> Dim RngColumn As Range
> Dim ColHeader As String
> 'Set the rng of the column headers
> Set RngHeaders = Range("A4", Cells(1,
> Columns.Count).End(xlToLeft))
> 'Get the column header to search for
> ColHeader = "ThisColumn"
> 'Find the column number of the column
> ColNum = RngHeaders.Find(What:=ColHeader, LookAt:=xlWhole).Column
> Set RngColumn = Range(Cells(5, ColNum), Cells(5,
> ColNum).End(xlDown))
> MsgBox RngColumn.Address ' For testing
> End Sub
> My code End
>
> Thanks
> Steve

--

Dave Peterson
 | 
Pages: 1
Prev: I need VBA Help for Excel spreadsheet -- Please!
Next: ounces vs. pounds