Results 1 to 6 of 6

Thread: changing parameters automiticallly in excel VB

  1. #1

    changing parameters automiticallly in excel VB



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    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

  3. #3
    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

  4. #4
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    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

  5. #5
    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

  6. #6
    Seeker michaelnicolas's Avatar
    Join Date
    Apr 2012
    Location
    Nicosia, Cyprus
    Posts
    11
    Articles
    0
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •