Excel - more information

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
LVL 5
magentoAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
VipulKadiaCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry for the range 1:10 etc. read 2:10 row 1 is your master data you are copying when required.

Chris
0
 
magentoAuthor Commented:
Can you please let me know the most used objects in vba code?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
Davy2270Commented:
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
 
magentoAuthor Commented:
Thanks Chris
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.