changing parameters automiticallly in excel VB

[justmcd]

I have VB code that I have to change everytime I decrease or increase number of MH in "Branch 6" and also range cells in new cell is inserted or removed like "AI36".

Is there any way that way that I can do this automatically without everytime going to VB.

Regards
justmcd

[michaelnicolas]

I'm not sure how to follow up what you are trying to do.
To speed things up could you post back the exact lines of VB code you need to change manually?

[justmcd]

Hi michaelnicolas

Here is the VB code below:

What I want to change is "For iTemp = 1 To 21" instead of everytime coming into VB to change 21 when ever MH number increases or decreases.

Also when I decrease or increase MH number to stop coming to VB to change cell references. For example if I insert a row, reference "ai36" will change to cell ai37 and everytime i will have to change the cell in the VB code.


ublic Sub BRANCH6()
Dim iTemp As Integer

For iTemp = 1 To 21

Sheets("BRANCH6").Select
Range("ai36").Select
ActiveCell.FormulaR1C1 = CStr(iTemp)

Range("aj46").GoalSeek Goal:=0, ChangingCell:=Range("ai44")
Range("Ai52").Select
Selection.Copy
Sheets("BRANCH6").Select

'// Ad3 as the 'base', then offseet by the value of iTemp
Range("Ad3").Offset(iTemp, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
End Sub

[michaelnicolas]

Change your code as follows:

Public Sub BRANCH6()
 Dim iTemp As Integer
 Dim iEnd As Integer
 
 iEnd = Worksheets("BRANCH6").Range("A" & Rows.Count).End(xlUp).Row
 
   For iTemp = 1 To iEnd
 
      Worksheets("BRANCH6").Range("AI1").Rows.End(xlDown).Value = CStr(iTemp)
 
      Range("aj46").GoalSeek Goal:=0, ChangingCell:=Range("ai44")
      Range("Ai52").Select
      Selection.Copy
      Sheets("BRANCH6").Select
 
      '// Ad3 as the 'base', then offseet by the value of iTemp
      Range("Ad3").Offset(iTemp, 0).Select
 
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
   Next


End Sub

[justmcd]

Hi michaelnicolas

thanks for your response.

i have tried your code but I get a debug message on Range("aj46").GoalSeek Goal:=0, ChangingCell:=Range("ai44")

any idea why?

Regards
justmcd

[michaelnicolas]

Hi,
I don't think there is something wrong with the code. It's the data, on that particular cell the value is 0. Check your data because I didn't change that part of the code.