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).
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