Consulting

Results 1 to 2 of 2

Thread: Call macro pending on value

  1. #1
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location

    Call macro pending on value

    I have 5 status cells with a validation. I would a cell to tell me what the updated status is.

    example

    Cells
    J11
    J15
    J19
    J23
    J27

    If J27 = "" Then Go To J23

    Else If J27 <> "" Then
    If J27 = "AVAILABLE", "RETIRE", OR "SHIPPING" tHEN Range("AA1") = Value

    Else ("AA1") = "WIP"

    If J23 = "" Then Go To J19

    Else If J23 <> "" Then
    If J23 = "AVAILABLE", "RETIRE", OR "SHIPPING" tHEN Range("AA1") = Value

    Else ("AA1") = "WIP"

    If J19 = "" Then Go To J15

    Else If J19 <> "" Then
    If J19 = "AVAILABLE", "RETIRE", OR "SHIPPING" tHEN Range("AA1") = Value

    Else ("AA1") = "WIP"

    And so on until J11 At some point it should find something and then when Saved it will look at Cell AA1 and know the latest update.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [vba]

    Dim TestCell As Range
    Dim i As Long

    For i = 27 To 11 Step -4

    If Cells(i, "J").Value <> "" Then

    Set TestCell = Cells(i, "J")
    Exit For
    End If
    Next i

    If TestCell Is Nothing Then

    Range("AA1").Value = "WIP"
    ElseIf TestCell.Value = "AVAILABLE" Or _
    TestCell.Value = "RETIRE" Or _
    TestCell.Value = "SHIPPING" Then

    Range("AA1").Value = TestCell.Value
    Else

    Range("AA1").Value = "WIP"
    End If
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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