Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - more information

Posted on 2011-10-18
10
Medium Priority
?
239 Views
Last Modified: 2012-05-12
Can some one please help me understand abt the range,cell,rows and the lines below.

  lRow = Range("A" & Rows.Count).End(xlUp).Row
  For each cell in Range("B2:B" & lRow)
    If Range("A" & cel.Row) <> "" Then Range("B1").Copy Destination:=cel
  Next cel
0
Comment
Question by:magento
9 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 36990731
Find last cell with data in all of column A
  lRow = Range("A" & Rows.Count).End(xlUp).Row
Using column A row range check each cell in column B
  For each cell in Range("B2:B" & lRow)
If the column A data is empty then B1 is copied to a cell address recorded by variable cell
    If Range("A" & cel.Row) <> "" Then Range("B1").Copy Destination:=cel
  Next cel

It is poss that the statement for each cell should be for each cel which I assume based on cell is not in scope of your fragment

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36990736
Note in finding last cell with detain column a a1:a10 could be blank but if A11 has data 11 will be the return for loop on B as b1:B11 and B1:B10 would be blank in this instance.

Chris
0
 
LVL 8

Expert Comment

by:VipulKadia
ID: 36990740
lRow = Range("A" & Rows.Count).End(xlUp).Row
This line will assing last row number in column-A to 'lRow' variable.

Now, following loop will check for Row2 to lRow in Column-B that if a particular cell in Column-A is not blank then copy corresponding cell from Column-B to cell in Column-A

  For each cell in Range("B2:B" & lRow)
    If Range("A" & cel.Row) <> "" Then Range("B1").Copy Destination:=cel
  Next cel
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36990743
Sorry for the range 1:10 etc. read 2:10 row 1 is your master data you are copying when required.

Chris
0
 
LVL 5

Author Comment

by:magento
ID: 36990815
Can you please let me know the most used objects in vba code?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 36990828
Most used objects ... I would hesitate to suggest it depends on your application and role for example ranges in excel and record sets in access with a smidgins of mail item in outlook, shape in ppt.

The list could be endless but scoping to your code range and excel!

Chris
0
 
LVL 3

Expert Comment

by:Davy2270
ID: 36991266
My answer is not directly to the point, but may give you hints an idea's for different ways to complete your entire 'project'.
Your code explained in worksheet operation would be this:

in B2 you type: =if(A2="","","$B$1)

Then you fill down the formula to the last filled cell in column A.

The advantage of working with formula's in this case is that you have immediate and automatic result.

Regards,
Davy
0
 
LVL 5

Author Comment

by:magento
ID: 36997463
Thanks Chris
0
 
LVL 50
ID: 37419380
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question