Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
show selective data - help in code
When you Set a variable as a range, that variable then takes the
characteristics of the range it has been set to. i.e. Set rng = Sheets(1).Range("A1") would make rng the same as cell A1. For the value: x = rng.Value, for the cell reference in A1 format: addr = rng.Address, to add to another cell value: tot = rng.Value + Range("B1").Value But, you cannot Set rng to the value or an object like: Set rng = Range.Name(DSOCML").Value You can Set rng = ActiveSheet.Range("DSOCML") Then x = rng.Value 'which would be the value of a range named DSOCML Remember all rules that apply to the range object applies to the object variable. Your second question is a little confusing. But if rng is equal to DSOCML named range, then the For Each statement will look at each cell in that range. Then if the searched cell equals the criteria, you would have the next line of code issue a command to do, or not to do, something. I don't comprehend "brought in" so , you would have to decide how to write the command line. The third question is yes. In VBA the variable must be initialized by making it equal to something, even if it is "" or zero. Declaring the variable in a Dim statement will reserve the memory for it until you use it the first time: Dim tsum As Double 'Integer with floating decimal tsum = 35.6 'Initialize the variable with a value "Eddy Stan" wrote in message ... Hi Question 1 ' the below line works Set rng = sh2.Range(sh2.Cells(4, 2), sh2.Cells(Rows.Count, 1).End(xlUp)) ' but how can i allot the data in range name "DSOCML" in to Set rng ' Can i substitute the above line like below line (now it gives error) ' Set rng = Range.Name("DSOCML").Value Question 2 For Each cell In rng If cell.Offset(0, 1) = Customer_lookfor then ' the above 2 lines looks for all cells in the range "DSOCML" for customer code, so if it finds anywhere, that data is brought in, which is wrong. I want to bring only if the customer lookfor is found in first column Question 3 do we have to initialise zero like below tsum = 0 xsum = 0 ysum = 0 cant we store like store 0 to tsum, xsum, ysum Urgent folks, please help me |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
show selective data - help in code
Hi
thank you. I know i am late in thanking eddy stan "JLGWhiz" wrote: When you Set a variable as a range, that variable then takes the characteristics of the range it has been set to. i.e. Set rng = Sheets(1).Range("A1") would make rng the same as cell A1. For the value: x = rng.Value, for the cell reference in A1 format: addr = rng.Address, to add to another cell value: tot = rng.Value + Range("B1").Value But, you cannot Set rng to the value or an object like: Set rng = Range.Name(DSOCML").Value You can Set rng = ActiveSheet.Range("DSOCML") Then x = rng.Value 'which would be the value of a range named DSOCML Remember all rules that apply to the range object applies to the object variable. Your second question is a little confusing. But if rng is equal to DSOCML named range, then the For Each statement will look at each cell in that range. Then if the searched cell equals the criteria, you would have the next line of code issue a command to do, or not to do, something. I don't comprehend "brought in" so , you would have to decide how to write the command line. The third question is yes. In VBA the variable must be initialized by making it equal to something, even if it is "" or zero. Declaring the variable in a Dim statement will reserve the memory for it until you use it the first time: Dim tsum As Double 'Integer with floating decimal tsum = 35.6 'Initialize the variable with a value "Eddy Stan" wrote in message ... Hi Question 1 ' the below line works Set rng = sh2.Range(sh2.Cells(4, 2), sh2.Cells(Rows.Count, 1).End(xlUp)) ' but how can i allot the data in range name "DSOCML" in to Set rng ' Can i substitute the above line like below line (now it gives error) ' Set rng = Range.Name("DSOCML").Value Question 2 For Each cell In rng If cell.Offset(0, 1) = Customer_lookfor then ' the above 2 lines looks for all cells in the range "DSOCML" for customer code, so if it finds anywhere, that data is brought in, which is wrong. I want to bring only if the customer lookfor is found in first column Question 3 do we have to initialise zero like below tsum = 0 xsum = 0 ysum = 0 cant we store like store 0 to tsum, xsum, ysum Urgent folks, please help me |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for Selective Printing | Excel Programming | |||
Copying selective data | Excel Discussion (Misc queries) | |||
Selective Data | Excel Programming | |||
VBA Code for Selective formula writing | Excel Programming | |||
how read data downloaded on cd-rw, now show only code? | New Users to Excel |