Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Gather data from a row and place it into another sheet

Status
Not open for further replies.

Marclem

Technical User
Aug 5, 2003
87
US
Hi Everyone,

I have the below vba script (attached the spreadsheet), which is running perfectly, it does a loop on sheet name MID YEAR REVIEW looking for numeric values found on Funds then copy the data to another sheet named Nav Export, once it finds another numeric data from another fund it then copy it to the next row on Nav Export sheet.

I have put in bold and as well highlighted the fund code as an example. I need now to find a way to as well have the next fund number 49903 data be provided (if there are data found in MID YEAR REVIEW) ON Nav Export sheet. I think I am on the right track, I tried copying the bold vba code changing it from 49902 to 49903 (where it was hightlighed) and adding to the end of the script right before the highlighted orange code "ActiveCell.Offset(6, -15).Select" but I get a message saying Next requires a For, I think I am struggling with how to properly put the ActiveCell.Offset(0, 1).


Code:
Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B36")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B36").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                        For x = 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        If Worksheets("Nav Export").Range("F" & ExportRow).Value = "GENADMIN" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "INDIRECT" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FUNDRAIS" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000004" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000005" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "MCARD005M" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "UNRESTR" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FIELDADM" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFMARGIN" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFSALE" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFRENTAL" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFOVERSPEND" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFUNALLOW" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFDONOR" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFINVEST" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "TRANSITION" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "DIRECTNEW" Then
            Worksheets("Nav Export").Range("W" & ExportRow).Value = 29100
            Else
            Worksheets("Nav Export").Range("W" & ExportRow).Value = 32950
            End If
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    
                    ActiveCell.Offset(1, -12).Select
                Next
                ActiveCell.Offset(2, 0).Select
                
               [b] For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value <> 0 Then
                        Month = ActiveCell.Offset(-18, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", [highlight #FCE94F]49902[/highlight], FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        If Worksheets("Nav Export").Range("F" & ExportRow).Value = "GENADMIN" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "INDIRECT" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FUNDRAIS" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000004" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000005" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "MCARD005M" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "UNRESTR" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FIELDADM" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFMARGIN" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFSALE" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFRENTAL" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFOVERSPEND" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFUNALLOW" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFDONOR" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFINVEST" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "TRANSITION" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "DIRECTNEW" Then
                            Worksheets("Nav Export").Range("W" & ExportRow).Value = 29100
                        Else
                            Worksheets("Nav Export").Range("W" & ExportRow).Value = 32950
                        End If
                         ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                Next
[/b]                    
                                               
            [highlight #FCAF3E]ActiveCell.Offset(6, -15).Select[/highlight]
            
           Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub

 
 http://files.engineering.com/getfile.aspx?folder=766bd541-c5de-4179-9110-8894003ae6eb&file=2017_Mid_Year_Budget_Review-Template_06.13.2017_-_Copy.xlsm
Hi,

"I get a message saying Next requires a For"

I compiled your code and did not get and error.

Then I ran your code and got 'Finished'.

????

Your code could be streamlined a whole lot. 1) You have several HUGE OR statements that I'd reduce to a lookup table. A lookup table is much easier to maintain than data embedded in code. 2) I'd avoid Selecting and Activating. Rather use referencing.

Just those three brief observations. Please post back to continue this conversation if necessary.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Next without For" error can be a result of missing "End If", sometimes in VBA compiler reports errors in other place, anyway something in the code is wrong.
As a pure vba alternative to the long condition in "If" you can try "Select Case" statement.

combo
 
I didn't take the time to copy all of your values, but using just 4 with combo's suggestion to replace those huge OR statements...
Code:
    Dim wsNavEx As Worksheet
    
    Set wsNavEx = Worksheets("Nav Export")
    
    Select Case wsNavEx.Range("F" & ExportRow).Value
        Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004"  'but wait! there's more...
            wsNavEx.Range("W" & ExportRow).Value = 29100
        Case Else
            wsNavEx.Range("W" & ExportRow).Value = 32950
    End Select


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Hi Skip,

Apologies for such a long response here:
(edit) (I did update the code as per your suggestion using the select case- but is not on my code below - don't want to create confusion since I had already posted the below code)

The code provided is working as expected what I am trying to do is having the same data copy as 49902 but now with 49903(which is the next row after 49902). I'm not completely sure how to. Below the result data.

Nav_Export_without_49903_data_fyqmjn.jpg


As you can see on above image, it gathers data from 49902 as per the code requirements, I would like to have the same requirements data gathering if there are values for any row showing 49903.

I have added (bold)the below code but I get the following result. I have highlighted where I think might be where I need to get the right (row and column) activecell.offset and maybe the correct month activecell.offsite for the 49903.

This is a screenshot of the data I get (which as you can see data is not pulling correctly - at least column R is pulling 49903 numbers right):
Nav_Export_with_49903_d3emrp.jpg



Code:
Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B36")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B36").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                        For x = 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        If Worksheets("Nav Export").Range("F" & ExportRow).Value = "GENADMIN" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "INDIRECT" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FUNDRAIS" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000004" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000005" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "MCARD005M" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "UNRESTR" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FIELDADM" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFMARGIN" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFSALE" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFRENTAL" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFOVERSPEND" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFUNALLOW" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFDONOR" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFINVEST" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "TRANSITION" Or _
            Worksheets("Nav Export").Range("F" & ExportRow).Value = "DIRECTNEW" Then
            Worksheets("Nav Export").Range("W" & ExportRow).Value = 29100
            Else
            Worksheets("Nav Export").Range("W" & ExportRow).Value = 32950
            End If
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    
                    ActiveCell.Offset(1, -12).Select
                Next
                ActiveCell.Offset(2, 0).Select
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value <> 0 Then
                        Month = ActiveCell.Offset(-18, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", 49902, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        If Worksheets("Nav Export").Range("F" & ExportRow).Value = "GENADMIN" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "INDIRECT" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FUNDRAIS" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000004" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000005" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "MCARD005M" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "UNRESTR" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FIELDADM" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFMARGIN" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFSALE" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFRENTAL" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFOVERSPEND" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFUNALLOW" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFDONOR" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFINVEST" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "TRANSITION" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "DIRECTNEW" Then
                            Worksheets("Nav Export").Range("W" & ExportRow).Value = 29100
                        Else
                            Worksheets("Nav Export").Range("W" & ExportRow).Value = 32950
                        End If
                         ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
             [highlight #FCE94F]   Next
                    
                   [b] ActiveCell.Offset(1, -12).Select
                
                ActiveCell.Offset(3, 0).Select[/highlight]
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value <> 0 Then
                        Month = ActiveCell.Offset([highlight #FCE94F]-18, 0[/highlight]).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", 49903, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        If Worksheets("Nav Export").Range("F" & ExportRow).Value = "GENADMIN" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "INDIRECT" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FUNDRAIS" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000004" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URF000005" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "MCARD005M" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "UNRESTR" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "FIELDADM" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFMARGIN" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFSALE" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFRENTAL" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFOVERSPEND" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFUNALLOW" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFDONOR" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "URFINVEST" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "TRANSITION" Or _
                            Worksheets("Nav Export").Range("F" & ExportRow).Value = "DIRECTNEW" Then
                            Worksheets("Nav Export").Range("W" & ExportRow).Value = 29100
                        Else
                            Worksheets("Nav Export").Range("W" & ExportRow).Value = 32950
                        End If
                         ExportRow = ExportRow + 1
                        End If[/b]
                    ActiveCell.Offset(0, 1).Select
                Next
                                               
            ActiveCell.Offset(6, -15).Select
            
           Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub
 
You're gonna have to help me out.

Somehow, you make a Responsible Center selection in C5, then run your code to load the Nav Export sheet. But that code only grabs the associated accounts, not the TOTAL INDIRECT RECOVERY -Direct and -Subawards, which is what you want.

So please explain the logic for selecting any specific RC.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
There is no reason to repeat the code 3 different times. You could do it once with the following changes (in Bold and Strikeout):
For x = 1 To 15 17
If x < 15 Then AccountNumber = Range("A" & ActiveCell.Row).Value Else AccountNumber = 44903 + x - 15

Additionally, the line ActiveCell.Offset(1, -12).Select may need to be modified so that it would offset properly. For example (Note: The code is not finished):
If x < 15 Then ActiveCell.Offset(1, -12).Select Else ...
 
I'm possibly missing something, but your wokkbook could be massively improved by a simple redesign of your MID YEAR REVIEW sheet. You simply need the FUND Nbr and Fund Name in two separate columns, just ONE row of Headings with Filters.

You would select the Fund Name: GENADMIN, to display the data for that group. Finally the TOTALS you are looking for cane simply be accomploshed using the SUBTOTAL(190,range) formula.

That data to export are the visible cells in the table.

I'd also make the table a Structured Table. ONE TABLE in the mid year review sheet.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Hi Skip,

Apologies for will try to explain better here:

Below is the latest updated code which have your select commands (thanks for that).

If you run it and then check the Nav Export sheet you will see that it copied from the MID YEAR REVIEW sheet and it shows 49902. So basically:

i.e. Under GENADMIN (row 36 col B) If there are entered any number on any month from columns E-P under rows 37-55 then as per the code requirements it will copy the data and put on nav export sheet then it goes down to next row 60 -col B and it does the same process over again, then starts again at row 84 colB... so basically after around every 23 rows it needs to check and see if there are any numbers on the month - then if there are copy data to nav export.

So far the code runs ok looking up to the 49902 (i.e.) row 54, 78, 102... ( I have in bold the code where it runs to find any 49902 data under the months) then loops thru and if it finds data it adds to the nav export in the correct column as per the code requirement.

Now I need to have the 49903 data to be added (same as 49902) - if there any number on any month then add to nav export in the correct column as per the code requirement.



Code:
Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B36")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B36").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                        For x = 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                
                Dim wsNavEx As Worksheet
                    Set wsNavEx = Worksheets("Nav Export")
    
                    Select Case wsNavEx.Range("F" & ExportRow).Value
                    Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                        wsNavEx.Range("W" & ExportRow).Value = 29100
                    Case Else
                        wsNavEx.Range("W" & ExportRow).Value = 32950
                    End Select
                
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    
                  [b]  ActiveCell.Offset(1, -12).Select
                Next
                ActiveCell.Offset(2, 0).Select
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value <> 0 Then
                        Month = ActiveCell.Offset(-18, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", 49902, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        
                    Dim wsNvEx As Worksheet
                        Set wsNvEx = Worksheets("Nav Export")
                        Select Case wsNavEx.Range("F" & ExportRow).Value
                        Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                            wsNvEx.Range("W" & ExportRow).Value = 29100
                        Case Else
                            wsNvEx.Range("W" & ExportRow).Value = 32950
                        End Select
                        
                         ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                Next[/b]
                    
                                                                                      
            ActiveCell.Offset(6, -15).Select
            
           Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub
 
Take there two statements out and put them at the top, after your last Dim statement. They do not belong inside any loop!

Now focus on the loops...
Code:
While
   For x = 1 To 15
      For y = 1 To 12

      Next y
   Next x

   For y = 1 to 12

   Next y
Wend

I think that you ought to read zelgar's post. Extend the x limit from 15 to 17. Do one set of logic for x<16 and another for 16 and 17. Use a...
Code:
Select Case x
   Case 1 To 15

   Case 16

   Case 17

End Select


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Just a very minor point.
You declare several variables in your Sub:
[tt]
Dim Start [blue]As Range[/blue]
Dim FundName
Dim ExportRow
Dim AccountNumber
Dim Month
Dim RC
Dim BudgetName
Dim Description
Dim Task
Dim i [blue]As Integer[/blue]
[/tt]
Only two of them have a specified Type, the rest of them are Variants.
You may want to declare their types as well.

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
Hi Skip and Zelgar,

Thank you for your support.

Below what I have - I still need to work on the offset coding (can you please help me on offset statements), I am getting a debug error at the end where it says " ActiveCell.Offset(6, -15).Select"

Can you let me know if my case statement is correct?
Code:
Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B36")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B36").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                
                For x = 1 To 17
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                Select Case x
                Case 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                Case 16
                AccountNumber = 44902 + x - 16
                Case 17
                AccountNumber = 44903 + x - 15
                End Select
                
                'If x < 15 Then
                'AccountNumber = Range("A" & ActiveCell.Row).Value
                'Else
                'AccountNumber = 44903 + x - 16
                'End If
                
                
               For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                
                Dim wsNavEx As Worksheet
                    Set wsNavEx = Worksheets("Nav Export")
    
                    Select Case wsNavEx.Range("F" & ExportRow).Value
                    Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                        wsNavEx.Range("W" & ExportRow).Value = 29100
                    Case Else
                        wsNavEx.Range("W" & ExportRow).Value = 32950
                    End Select
                
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    ActiveCell.Offset(1, -12).Select
                Next
                'ActiveCell.Offset(2, 0).Select

 ActiveCell.Offset(6, -15).Select

 Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub
 
Last statement in the previous loop you move 12 columnc left then another 15? I don't think so.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Since the ActiveCell.Offset(6, -15).Select is outside of the loop, why do you need it? I have a feeling that you need to modify the ActiveCell.Offset(1, -12).Select with an IF statement or using CASE command for conditions when X = 16 & 17 when you're evaluating when the Account Number is 44902 and 44903.
 
Hi Guys,

Below what I have changed on statements, please note that I have changed from 1 to 19 since the 49902 is at row 18 and 49903 is at row 19 located at the MID YEAR sheet. When I ran the macro I am only getting the data from rows 1 to 15, note that row 16 and 17 data do not need to be copy to the nav export sheet. Please help with this, it is a really important report I need to finalize and I am not a vba expert.

So basically only row data from 1-15 and 18 and 19 need to be copied if there numbers on the months.


I am attaching the excel sheet with below macro already in place.


Thank you for all your help.

Code:
Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B36")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B36").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                
                [highlight #FCE94F][b]For x = 1 To 19
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                Select Case x
                Case 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                Case 18
                AccountNumber = 44902 + x - 18
                Case 19
                AccountNumber = 44903 + x - 17
                End Select[/highlight]
                
                'If x < 15 Then
                'AccountNumber = Range("A" & ActiveCell.Row).Value
                'Else
                'AccountNumber = 44903 + x - 16
                'End If[/b]
                
                
               For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                
                Dim wsNavEx As Worksheet
                    Set wsNavEx = Worksheets("Nav Export")
    
                    Select Case wsNavEx.Range("F" & ExportRow).Value
                    Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                        wsNavEx.Range("W" & ExportRow).Value = 29100
                    Case Else
                        wsNavEx.Range("W" & ExportRow).Value = 32950
                    End Select
                
                
                        ExportRow = ExportRow + 1
                        
                        End If
                        
                  [highlight #FCE94F] [b] ActiveCell.Offset(0, 1).Select
                    Next
                    If x < 15 Then
                    ActiveCell.Offset(1, -12).Select
                    ElseIf x = 18 Then
                    ActiveCell.Offset(18, -12).Select
                    ElseIf x = 19 Then
                    ActiveCell.Offset(19, -12).Select
                    End If
                    'If
                    'ActiveCell.Offset(18, -12).Select
                Next
                'ActiveCell.Offset(2, 0).Select

 'ActiveCell.Offset(0, 0).Select[/b]
[/highlight]
 Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub

 
 http://files.engineering.com/getfile.aspx?folder=86911c04-4553-48c7-8beb-22c8ea122d9d&file=2017_Mid_Year_Budget_Review-Template_06.13.2017.xlsm
You loop from 1 to 19, but you want to by-pass 16 and 17
How about:

Code:
              For x = 1 To 19[blue]
                AccountNumber = -1[/blue]
                
                Select Case x
                  Case 1 To 15
                    AccountNumber = Range("A" & ActiveCell.Row).Value
                  Case 18
                    AccountNumber = 44902 + x - 18
                  Case 19
                    AccountNumber = 44903 + x - 17
                End Select
                [blue]
            If AccountNumber > -1 Then[/blue]
               For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                
                Dim wsNavEx As Worksheet
                    Set wsNavEx = Worksheets("Nav Export")
    
                    Select Case wsNavEx.Range("F" & ExportRow).Value
                    Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                        wsNavEx.Range("W" & ExportRow).Value = 29100
                    Case Else
                        wsNavEx.Range("W" & ExportRow).Value = 32950
                    End Select
                
                
                        ExportRow = ExportRow + 1
                        
                        End If
                        
                    ActiveCell.Offset(0, 1).Select
                    Next
                    If x < 1[red]6[/red] Then
                      ActiveCell.Offset(1, -12).Select
                    ElseIf x = 18 Then
                      ActiveCell.Offset(18, -12).Select
                    ElseIf x = 19 Then
                      ActiveCell.Offset(19, -12).Select
                    End If
                    'If
                    'ActiveCell.Offset(18, -12).Select[blue]
                 End If[/blue]
                Next

As a side note -
breakpoints, stepping through your code, and hovering over the variables with your mouse to see their values will help you to find the problems in your code that often leads to solution.

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
Hi Guys,

After trying all your suggestions and due to time constraints to get this report running, I ended up (I know it can still be streamed line a bit) with the below statements and it is working as expected. I would appreciate if possible help on making it shorter:

Thank you for all guys do to help those in need!


Code:
Sub Test_Macro2()
'
' Test_Macro Macro
'
    
    Dim Start As Range
    Dim FundName
    Dim ExportRow
    'Dim ExportValue
    Dim AccountNumber
    Dim Month
    Dim RC
    Dim BudgetName
    Dim Description
    Dim Task
    Dim i As Integer
    
    
    Sheets("MID YEAR REVIEW").Select
    
    'Start = Range("B36")
    RC = Range("C5").Value
    Description = Range("C6").Value
    BudgetName = Range("C7").Value
    ExportRow = 1
    
    If RC <> "" Then
        'Clear our Export Tab
        Worksheets("Nav Export").Range("A:Y").ClearContents
        
        Range("B36").Select
        Set Start = Range(ActiveCell.Address)
        'Start.Select
        
        While ActiveCell.Value <> ""
            FundName = ActiveCell.Value
            Task = Range("b" & ActiveCell.Row - 1).Value
            ActiveCell.Offset(1, 3).Select
                        For x = 1 To 15
                AccountNumber = Range("A" & ActiveCell.Row).Value
                
                
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) Then
                        Month = ActiveCell.Offset(-x, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", AccountNumber, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        
                'Choose between 29100 or 32950 between row 1 to 15
                Dim wsNavEx As Worksheet
                    Set wsNavEx = Worksheets("Nav Export")
    
                    Select Case wsNavEx.Range("F" & ExportRow).Value
                    Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                        wsNavEx.Range("W" & ExportRow).Value = 29100
                    Case Else
                        wsNavEx.Range("W" & ExportRow).Value = 32950
                    End Select
                
                        ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                    Next
                    
                    ActiveCell.Offset(1, -12).Select
                Next
                ActiveCell.Offset(2, 0).Select
                
                'Gather acct number 49902
                For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value <> 0 Then
                        Month = ActiveCell.Offset(-18, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", 49902, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                    'Choose between 29100 or 32950 on row 18
                    Dim wsNvEx As Worksheet
                        Set wsNvEx = Worksheets("Nav Export")
                        Select Case wsNvEx.Range("F" & ExportRow).Value
                        Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                            wsNvEx.Range("W" & ExportRow).Value = 29100
                        Case Else
                            wsNvEx.Range("W" & ExportRow).Value = 32950
                        End Select
                        
                         ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                Next
                    ActiveCell.Offset(1, -12).Select
                    
                    'Gather acct number 49903
                    For y = 1 To 12
                    If ActiveCell.Value <> "" And IsNumeric(ActiveCell) And ActiveCell.Value <> 0 Then
                        Month = ActiveCell.Offset(-19, 0).Value
                        For i = 1 To 10
                            Worksheets("Nav Export").Range(Choose(i, "A", "C", "D", "E", "F", "K", "I", "Q", "R", "V") & ExportRow).Value = _
                                Choose(i, Month, RC & "16MID", "G/L Account", 49903, FundName, Task, RC, Description, _
                                ActiveCell.Value, "G/L Account")
                        Next i
                        Worksheets("Nav Export").Range("R" & ExportRow).NumberFormat = "#,##0.00"
                        If BudgetName <> "" Then
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = BudgetName
                        Else
                            Worksheets("Nav Export").Range("S" & ExportRow).Value = "MIDYEAR"
                        End If
                        'Choose between 29100 or 32950 on row 19
                    Dim wsNvix As Worksheet
                        Set wsNvix = Worksheets("Nav Export")
                        Select Case wsNvix.Range("F" & ExportRow).Value
                        Case "GENADMIN", "INDIRECT", "FUNDRAIS", "URF000004", "URF000005", "MCARD005M", "UNRESTR", "FIELDADM", "URFMARGIN", "URFSALE", "URFRENTAL", "URFOVERSPEND", "URFUNALLOW", "URFDONOR", "URFINVEST", "TRANSITION", "DIRECTNEW"
                            wsNvix.Range("W" & ExportRow).Value = 29100
                        Case Else
                            wsNvix.Range("W" & ExportRow).Value = 32950
                        End Select
                        
                         ExportRow = ExportRow + 1
                        End If
                    ActiveCell.Offset(0, 1).Select
                Next
                
                                                                                      
            ActiveCell.Offset(5, -15).Select
            
           Wend
           Range("C10").Select
           MsgBox ("Finished!")
      Else
        MsgBox ("You must enter a Responsibility Center code to continue")
      End If
End Sub
 
[highlight #FCE94F]These[/highlight] statements do NOT belong inside a loop!!!

Plz locate them near the beginning of your code, after the Dim i As Integer statement...
Code:
Dim i As Integer
[highlight #FCE94F]Dim wsNvix As Worksheet
Set wsNvix = Worksheets("Nav Export")[/highlight]
'....

After the last End If and before End Sub, please add [highlight #FCAF3E]this[/highlight] statement...
Code:
'...
[highlight #FCAF3E]Set wsNvix = Nothing[/highlight]
End Sub

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top