Need Adaptable Macro

[Michael Dobony]

I have the following absolute ranges in Excel 2010 VBA macro. I have
variables assigned with the column number, as listed below. I am having
difficulty replacing the column letters with a Cell(row,column) format with
the assigned variables. I've tried numerous variations using cells and
columns ranges, but nothign works. Other changes, such as
Range("JB3").Activate work when changed to Cells(3,USD_Gross).Activate, so
I know the variables have the correct values. The variables are dimensioned
as Integers.

I have about 20+pages of macros that need these changes. They're basically
taking raw data (formats and columns can move, causing serious issues with
the macros as is) and reformatting it and bringing in summaries. It worked
fine when it was built originally, but changed formats for the raw data
messed things up considerably. I need to make this adaptable to increased
columns.

Can anybody help me figure out this mess? I need to get the specific cell
and column references converted to use the variables. Thanks.

For Each rngA In Range("JB3:JB" & Range("JE60000").End(xlUp).Row)
If WorksheetFunction.Trim(Range("JB" &
ActiveCell.Row).Value) = 0 Or WorksheetFunction.Trim(Range("JC" &
ActiveCell.Row).Value) = 0 Or WorksheetFunction.Trim(Range("M" &
ActiveCell.Row).Value) = 0 Then

JB=USD_Gross
JC=USDNet
JE=ProdSrv

 

[GS]

Try assigning the range to an object variable, then ref the appropriate
column index of the rng...

<pseudo code>
Dim rnge As Range, c As Range
Set rng = Range(...) '//edit to suit

For Each c In rng
With WorksheetFunction
If .Trim(Cells(c.Row, rng.Columns(1))) = 0 _
Or .Trim(Cells(c.Row, "JC")) = 0 _
Or .Trim(Cells(c.Row, "M")) = 0
End With 'WorksheetFunction
Next 'c

...where the 1st condition ref the column index of the rng object, and
the remaining conditions examples using the column letter.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I should have mentioned that if your col vars (ie: USD_Gross) hold the
actual col index (not the index of the range) then...

.Trim(Cells(c.Row, USD_Gross))

...will work in place of the col labels ("JC","M") as well as the range
col index.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I should have mentioned that if your col vars (ie: USD_Gross) hold the
actual col index (not the index of the range) then...

.Trim(Cells(c.Row, USD_Gross))

...will work in place of the col labels ("JC","M") as well as the range
col index.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Something a bit more efficient since you only need to evaluate rows
once, not for every col in the range...

<pseudo code>
Dim rnge As Range, r As Range
Set rng = Range(...) '//edit to suit

For Each r In rng.Rows
With WorksheetFunction
If .Trim(Cells(r.Row, USD_Gross)) = 0 _
Or .Trim(Cells(r.Row, "JC")) = 0 _
Or .Trim(Cells(r.Row, "M")) = 0
End With 'WorksheetFunction
Next 'c

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Michael Dobony]

Something a bit more efficient since you only need to evaluate rows
once, not for every col in the range...

<pseudo code>
Dim rnge As Range, r As Range
Set rng = Range(...) '//edit to suit

For Each r In rng.Rows
With WorksheetFunction
If .Trim(Cells(r.Row, USD_Gross)) = 0 _
Or .Trim(Cells(r.Row, "JC")) = 0 _
Or .Trim(Cells(r.Row, "M")) = 0
End With 'WorksheetFunction
Next 'c

USD_Gross is the column number, as located by a Find(what"USD Gross Pd" . .
.. Etc. Is that what you mean by

This is also not helping me set the range in the first place using the
column index (column number in the R1C1 format)

 

[GS]

Set rng = Range(Cells(3, "JB"), Cells(Rows.Count.End(xlUp),Row,
Range(USD_Gross).Column) '//edit to suit

USD_Gross is the column number, as located by a Find(what"USD Gross
Pd" . . . Etc. Is that what you mean by

This is also not helping me set the range in the first place using
the column index (column number in the R1C1 format)

Watch for line wrap! The line that sets the rng object is a single
line.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Something a bit more self-documenting...

<pseudo code>
Dim rnge As Range, r As Range
Dim lEndRow&, lEndCol&

Const lStartRow& = 3:Const lStartCol& = Columns("JB") '//edit to suit
lEndCol = USD_Gross '//edit to suit
lEndRow = Cells(Rows.Count, lEndCol).End(xlUp).Row
Set rng = Range(Cells(lStartRow, lStartCol), Cells(lEndRow, lEndCol))

For Each r In rng.Rows
With WorksheetFunction
If .Trim(Cells(r.Row, "JB")) = 0 _
Or .Trim(Cells(r.Row, "JC")) = 0 _
Or .Trim(Cells(r.Row, "M")) = 0
End With 'WorksheetFunction
Next 'c

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Michael Dobony]

Something a bit more self-documenting...

<pseudo code>
Dim rnge As Range, r As Range
Dim lEndRow&, lEndCol&

Const lStartRow& = 3:Const lStartCol& = Columns("JB") '//edit to suit
lEndCol = USD_Gross '//edit to suit
lEndRow = Cells(Rows.Count, lEndCol).End(xlUp).Row
Set rng = Range(Cells(lStartRow, lStartCol), Cells(lEndRow, lEndCol))

For Each r In rng.Rows
With WorksheetFunction
If .Trim(Cells(r.Row, "JB")) = 0 _
Or .Trim(Cells(r.Row, "JC")) = 0 _
Or .Trim(Cells(r.Row, "M")) = 0
End With 'WorksheetFunction
Next 'c

Your earlier macro is causing errors (forgot paper at work) in:

Set rng = Range(Cells(3, "JB"), Cells(Rows.Count.End(xlUp),Row,
Range(USD_Gross).Column)

It doesn't like the Count part. Instead, I figured out how to get the
column name in a variable and substituting the fixed addresses for
variables. Everything is working now, but will probably take another day to
replace all the crazy formulas.

 

[GS]

Your earlier macro is causing errors (forgot paper at work) in:

Set rng = Range(Cells(3, "JB"), Cells(Rows.Count.End(xlUp),Row,
Range(USD_Gross).Column)

Yeah, I realized that after and is why I rewrote it using vars for the

It doesn't like the Count part. Instead, I figured out how to get the
column name in a variable and substituting the fixed addresses for
variables. Everything is working now, but will probably take another
day to replace all the crazy formulas.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion