defining names in a macro

[guidop12]

With the below coding and example of my spreadsheet

Dim myRangeD As Range, myRangeF As Range, myRangeC As Range, myRangeE As
Range, _
myRangeH As Range, myRangeG As Range
Worksheets("sheet1").Activate
Set myRangeD = Worksheets("Sheet1").Range("duplex")
Set myRangeF = Worksheets("Sheet1").Range("color")
Set myRangeC = Worksheets("Sheet1").Range("simplex")
Set myRangeE = Worksheets("sheet1").Range("b_w")
Set myRangeH = Worksheets("Sheet1").Range("price")
Set myRangeG = Worksheets("Sheet1").Range("of_sheets")


If myRangeD.Text = "X" And myRangeF.Text = "X" Then
myRangeH = (myRangeG * 2 * 0.045)
End If
If myRangeC.Text = "X" And myRangeF.Text = "X" Then
myRangeH = (myRangeG * 1 * 0.045)
End If
If myRangeD.Text = "X" And myRangeE.Text = "X" Then
myRangeH = (myRangeG * 2 * 0.008)
End If
If myRangeC.Text = "X" And myRangeE.Text = "X" Then
myRangeH = (myRangeG * 1 * 0.008)
End If
End Sub

Job Machine Simplex Duplex B/W Color # of Sheets Price

79815 6500 X X 2965 $237.20

79700 C500 X X 420 $37.80

79718 6500 X X 25 $0.10

Each column has a defined name which highlights the whole column.
My question is why does my macro only work if I change this line to be
Set myRangeD = Worksheets("Sheet1").Range("D3") and not the name

 

[Dave Peterson]

Worksheets("Sheet1").Range("duplex") is a whole column?

If it is, then things like this:
If myRangeD.Text = "X"
aren't valid.

With multiple cells, the .text property will return Null.
You could use .Text to refer to what's displayed in a single cell, though.

If you used:
If myRangeD.Value = "X"
you'd have a different problem.

For multiple cells, .value returns an 2 dimensional array (x rows by y
columns). And you can't compare an array to that single value. (You could
compare a single element of that array to a single value, though.)

 

[guidop12]

hi,

Yes the Worksheets("Sheet1").Range("duplex") is a whole column.
the reason i put the myrangeD.text is if I don't have it in there when I run
the macro it comes up with a type mismatch error.

 

[Dave Peterson]

If you want to look at the value or text of just one cell in that range, you
could use:

if myranged.cells(1).value = "x"
for the first cell in that single column range.

if myranged.cells(99).value = "x"
would look at the 99th cell (row 99) of that range.

I guess my question is this: Is there a reason you don't just use a formula
instead of the macro?

=IF(AND(D1="x",F1="x"),G1*2*0.045,
IF(AND(C1="x",F1="x"),G1*0.045,
IF(AND(D1="x",E1="x"),G1*2*0.008,
IF(AND(C1="x",E1="x"),G1*0.008,""))))

I'm guessing that myRangeD is column D, ..., myRangeH is column H. And the
formula would go in column H.

 

[guidop12]

I guess the reason why i used macros is because each row will contain a
differnet machine (ex: 6500 or c500 they are differnet printer types) so my
macros were corresponding with the differnet printers and the amount being
charge (because each printer has a differnet charge). My only frustration is
that the macros do work if I just have" Set myRangeD =
Worksheets("Sheet1").Range("d3")" which would be the current row that the
user is inputting in,, but it will not work if I have a name range

or if I do use a formula how can i distinguish between the printers

 

[guidop12]

Dave,

Disregard my last post, and I want to thank you, thank you, thank you for
steering me in the right direction. What I did was use your formula and then
made macros up for the differnet printer choices and it works great.

Again thank you

 

[Dave Peterson]

You could also use something like:

Dim myRow as long
myrow = activecell.row

with activesheet
if .cells(myrow,myRangeD.column).value = "X" _
and .cells(myrow,myrangeF.column).value = "X" then
.cells(myrow,myrangeH.column).value _
= .cells(myrow,myRangeG.column) * 2 * 0.045
elseif .....

I guess the reason why i used macros is because each row will contain a
differnet machine (ex: 6500 or c500 they are differnet printer types) so my
macros were corresponding with the differnet printers and the amount being
charge (because each printer has a differnet charge). My only frustration is
that the macros do work if I just have" Set myRangeD =
Worksheets("Sheet1").Range("d3")" which would be the current row that the
user is inputting in,, but it will not work if I have a name range

or if I do use a formula how can i distinguish between the printers