Need Help Looping with VBA

[wjack]

I'm trying to refresh data using a loop in VBA and aren't really sure how to proceed. Below is an image of what I'm trying to do. Basically I need to call out the country codes one at a time for use in a refresh with Hyperion Essbase. Once the data for a single code has been refreshed I want to append it to the bottom of an output table and then repeat until there are no more country codes.

Thanks in advance for the help!

 

[XLfriend777]

Looping is fairly simple. You could use a "While" loop as shown below. Hope this works for you.
Sub wjack()
Dim code As String
Dim i As Integer
i = 2 'Start at row 2
While Cells(i, 1) <> "" 'Moves down column A from row 2 to the first empty cell
code = Cells(i, 1).Value
Cells(3, 3).Value = code
Application.Calculate 'Refresh the data with the new value in C3

'Now move the data from C/D to F/G
Cells(i + 1, 6).Value = Cells(3, 3).Value
Cells(i + 1, 7).Value = Cells(3, 4).Value

i = i + 1
Wend
End Sub

 

[wjack]

Looping is fairly simple. You could use a "While" loop as shown below. Hope this works for you.
Sub wjack()
Dim code As String
Dim i As Integer
i = 2 'Start at row 2
While Cells(i, 1) <> "" 'Moves down column A from row 2 to the first empty cell
code = Cells(i, 1).Value
Cells(3, 3).Value = code
Application.Calculate 'Refresh the data with the new value in C3

'Now move the data from C/D to F/G
Cells(i + 1, 6).Value = Cells(3, 3).Value
Cells(i + 1, 7).Value = Cells(3, 4).Value

i = i + 1
Wend
End Sub

So what if the geographies range isn't in the first column? Is there a way to select the table range and then run the update loop? Also, will this work if some of the country codes are not integers (ex. "Total").

For the refresh, is it possible to run another sub (named "Update_Actuals")?

Thanks for the help Goeser, this is saving me so much time!

 

[XLfriend777]

Hi wjack

1. For the code - you just need to adjust the While loop so that it navigates the correct range. That is why I use Cells(x,y) where you can call variables. In this case I used Cells(i,1) [Row i, Column 1]
2. I dimensioned the variable code as String - Dim code as String - so that you can have all characters, not just integers
3. You can run another Sub simply by using: Call Update_Actuals - in place of the code Application.Calculate

Hope this helps.

 

[wjack]

So I modified the code and the "i = i +1" section of code doesn't seem to be functioning. The next row is not selected and the sub just ends. Code below:


Sub Update()

Sheets("Geographic Data").Select

Dim code As String
Dim i As Integer
i = 6 'Start at row 6
While Cells(i, 1) <> "" 'Moves down column A from row 2 to the first empty cell
code = Cells(i, 3).Value
Selection.Copy

Call Update_Actuals
i = i + 1
Wend
End Sub

Sub Update_Actuals()
'Update Actuals

Sheets("Actuals Data").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A7").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Range("E6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Sheets("Actuals Data").Select
Range("B6").Value = "OPEXIN"
Range("D6").Value = "TOTBU"


Sheets("Inputs").Select
Range("B3").Select
Selection.Copy

Sheets("Actuals Data").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' SendKeys "%y1drr", True
' DoEvents
' Range("A8").Select
' SendKeys "%y1y2b", True
' DoEvents

Sheets("Geographic Data").Select

End Sub