| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
defining names in a macro
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
defining names in a macro
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
defining names in a macro
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" wrote: 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 wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
defining names in a macro
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
defining names in a macro
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 "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
defining names in a macro
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
defining names in a macro
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 ..... guidop12 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with defining names | Excel Worksheet Functions | |||
| Defining Regions with Names | Excel Discussion (Misc queries) | |||
| Excel 2007 defining names (areas) | Excel Worksheet Functions | |||
| Defining Names in Excel | Excel Worksheet Functions | |||
| Defining non worksheet specific names... | Excel Worksheet Functions | |||
Linear Mode
