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?
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
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?
_________________________________
Best Regards
Nicolas Michael
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
Change your code as follows:
Code: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
_________________________________
Best Regards
Nicolas Michael
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
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.
Bookmarks