Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Interacting on Memory (XP' SP3)

    I have an issue with the StatusBar not always being updated after going through a loop in the code. I think it may be how my code is written and it's effect on memory. If I break out of the code then the statusbar shows the address of the last cell processed.

    Here is my code:
    Sub IndexCell()
    Application.ScreenUpdating = False
    Do Until ActiveCell.Offset(0, -1) = ""
    Application.StatusBar = "Working on Row: " & ActiveCell.Address
    If ActiveCell.Offset(0, -1) = "02055" Then
    oAcct = ActiveCell.Offset(0, -3)
    oBunit = ActiveCell.Offset(0, -1)
    oAcct_Dept = """" & oAcct & """" & "&" & """" & oBunit & """"

    ActiveCell.FormulaArray = "=INDEX(Database!A$1$50000,MATCH(" & oAcct_Dept & ",Database!A$1:A$50000&Database!B$1:B$50000,0) ,4)"
    If IsError(ActiveCell) = True Then ActiveCell = "Not Listed In Database"
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Finished"
    End Sub


    Your assistance is appreciated,
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Interacting on Memory (XP' SP3)

    Try this variation on your code. Since I don't have your worksheet, I have not tested this.

    <code>
    Sub IndexCell()
    Dim I As Long
    I = 0
    Do Until ActiveCell.Offset(I, -1) = ""
    Application.StatusBar = "Working on Row: " & ActiveCell.Address
    DoEvents
    If ActiveCell.Offset(I, -1) = "02055" Then
    oAcct = ActiveCell.Offset(I, -3)
    oBunit = ActiveCell.Offset(I, -1)
    oAcct_Dept = """" & oAcct & """" & "&" & """" & oBunit & """"

    ActiveCell.Offset(I, 0).FormulaArray = "=INDEX(Database!A$1$50000,MATCH(" _
    & oAcct_Dept & ",Database!A$1:A$50000&Database!B$1:B$50000,0) ,4)"
    If IsError(ActiveCell).Offset(I, 0) = True Then ActiveCell = "Not Listed In Database"
    End If
    I = I + 1
    Loop
    Application.StatusBar = False
    MsgBox "Finished"
    End Sub
    </code>
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Interacting on Memory (XP' SP3)

    Legare,

    I tried your rendition of the code and made a few minor tweaks to get it to work.

    One additional question: Instead of writing the array formula to the cell and then doing the test for an error: can the index results be sent to a variable instead of to a cell?

    Sub IndexCell()
    Dim I As Long
    I = 0
    Do Until ActiveCell.Offset(I, -1) = ""
    Application.StatusBar = "Working on Row: " & ActiveCell.Offset(I, 0).Address
    DoEvents
    If ActiveCell.Offset(I, -1) = "02055" Then
    oAcct = ActiveCell.Offset(I, -3)
    oBunit = ActiveCell.Offset(I, -1)
    oAcct_Dept = """" & oAcct & """" & "&" & """" & oBunit & """"

    ActiveCell.Offset(I, 0).FormulaArray = "=INDEX(Database!A$1$50000,MATCH(" & oAcct_Dept & ",Database!A$1:A$50000&Database!B$1:B$50000,0) ,4)"
    If IsError(ActiveCell.Offset(I, 0)) = True Then ActiveCell.Offset(I, 0) = "Not In Database"
    End If
    I = I + 1
    Loop
    Application.StatusBar = False
    MsgBox "Finished"
    End Sub

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Interacting on Memory (XP' SP3)

    The short answer is yes. However, without seeing your workbook, I am having a hard time figuring out what your routine is actually doing. The formula you are putting into the cell does not make sense to me. In particular, I can't figure out what "Database!A$1:A$50000&Database!B$1:B$50000" is doing. It does not look like a valid reference.

    To return the result to a VBA variable, you have to change that into a valid VBA statement, replace "INDEX" with "Application.WorksheetFunction.Index" and "MATCH with "Application.WorksheetFunction.Match", and change the ranges in the formula to VBA ranges (for example, replace "Database!A$1$50000" with "Worksheets("Database").Range("A$1$50000")". You will also need to surround the statement with "On Error Resume Next" and "On Error GoTo 0" since the statment will raise a VBA error if the Match or Index fails.
    Legare Coleman

Posting Permissions

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