You are here:

Excel/Looking for a starting row using VBA - Follow up

Advertisement


Question
QUESTION: Hi Tom,

I have a report that has three sections.

Section 1 - From row 1 to row 7. This is always fixed.
Section 2 - From row 10 to row X. The starting row is always fixed, but the ending row varies on each worksheet within the workbook.
Section 3 - The starting row will be 3 rows below the last row in section 2. For example, my last row in section 2 is row 16, my staring row in section will be row 19. I want a macro that I can identify 3 rows below the last row in section 2, and put a word "Information" in the first cell in that row. In this case, I want to put "Information" in cell A19.

Thanks in advance for your help.

Tim

ANSWER: Tim,

I assume that each row in section 2, starting with row 10 will have some non-empty cells in the row (no completely blank row in section 2).  So the first blank row that is found after row 10 is the first row after section 2. I then add 2 to that row number and put the word information in that row in column A:


Sub abc()
Dim sh As Worksheet
Dim i As Long

For Each sh In Worksheets
  i = 10
  Do While Application.CountA(sh.Rows(i)) > 0
    i = i + 1
  Loop
  ' at this point, i will point to the first blank row
  sh.Cells(i + 2, "A").Value = "Information"
Next
End Sub

Lightly tested, but that worked for me given the assumptions I stated and your description as I understand it.

--
Regards,
Tom Ogilvy
  

---------- FOLLOW-UP ----------

QUESTION: Thanks Tom. Your assumptions are correct. I tried the code and it worked.

Now I am trying to apply the code with some modifications to another report, but I am not getting expected results. Probably you can point out the issue in my code.

I think it would be helpful if I provide a brief description of my other report before I show the code. I have a master data sheet called "Sales Master Data". There are different product names in column B starting from cell B2 (B1 is the column header). Each product name in column B may have multiple rows with different sales data. The product name in column B will match the worksheet name in the workbook. I am trying to put the word "Sales" in each individual worksheet except the "Sales Master Data" sheet. The position of the word "Sales" will be similar to the other report, which is 3 lines below the last row in section 2. For some reason, the word "Sales" only appears on the first product worksheet, but not other product worksheets. Here is code:

Sub Sales()
Dim sht As Worksheet
Dim i As Long
Dim rng30 As Range

Set rng30 = Sheets("Sales Master Data").Range("B2")

   For Each sht In Worksheets
   i = 10
   Do While Application.CountA(sht.Rows(i)) > 0
     i = i + 1
   Loop
   
   If sht.Name = Left(rng30.Value, 31) Then
   sht.Select
   Cells(i + 2, "A").Select
   With Selection
       .Value = "Sales"
       .Font.Name = "Arial"
       .Font.Size = 10
       .Font.Bold = True
   End With
   
   End If

Next
End Sub

Thanks again Tom for your help,

Tim

Answer
Tim,

rng30 never changes what cell it refers to.  It always refers to Cell B2 of the Sales Master Data sheet.  So your IF test will only be passed for the sheet that has a name matching the value in B2.  


I would suggest something like this where you loop through all the values in column B starting in B2.  Now this code assumes that column B has data in it all the way to the last row you want processed.  If that is not the case, see the code after this code:


Sub Sales()
Dim sht As Worksheet
Dim i As Long
Dim rng30 As Range

Set rng30 = Sheets("Sales Master Data").Range("B2")
Do while rng30 <> ""
   set sht = Nothing
   on Error Resume Next
    set sht = Worksheets(rng30.Value)
   On Error goto 0
   if not sht is nothing then
     i = 10
     Do While Application.CountA(sht.Rows(i)) > 0
       i = i + 1
     Loop
     with sht.Cells(i + 2, "A")
       .Value = "Sales"
       .Font.Name = "Arial"
       .Font.Size = 10
       .Font.Bold = True
     End With
   
   End If
 set rng30 = rng30.offset(1,0)
Loop
End Sub


if there are empty cells in column B, then try this approach:

Sub Sales()
Dim sht As Worksheet
Dim i As Long
Dim rng30 As Range
Dim lastrow as Long

set sh = Worksheets("Sales Master Data")
lastrow = sh.Cells(sh.rows.count, "B").End(xlup).row
Set rng30 = sh.Range("B2")
Do while rng30.row <= lastrow
   set sht = Nothing
   on Error Resume Next
    set sht = Worksheets(rng30.Value)
   On Error goto 0
   if not sht is nothing then
     i = 10
     Do While Application.CountA(sht.Rows(i)) > 0
       i = i + 1
     Loop
     with sht.Cells(i + 2, "A")
       .Value = "Sales"
       .Font.Name = "Arial"
       .Font.Size = 10
       .Font.Bold = True
     End With
   
   End If
 set rng30 = rng30.offset(1,0)
Loop
End Sub


Both code samples are untested, so I suggest you test them or either on a copy of your workbook.  While untested, they should represent a practical approach to doing what you want.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.