Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro structure (Excel XP)

    I need to create a list of cell addresses as an edit report, for certain conditions such as " Cell value between 161000 and 179000". I would want to print the cell address and a message such as "This account needs such and such to approve" in a range on the worksheet. So how would one structure a macro that would write a cell address, then a message, then go to the next line and write the next cell address and next message, as the macro examines each cell in the selected range? I'm not sure if it has to re-locate itself on the next line of the report first, then re-examine all the cells all over again, or can it march down the list and move the "cell to be written to" location on the report in some way? Just an idea or perhaps a sample or model would be helpful..I'd like to tackle the code myself and submit for review. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro structure (Excel XP)

    Try this as starting point. It expects that you have selected a range, and will create a new worksheet with info. Adapt to your needs.

    Sub Report()
    Dim rngInput As Range
    Dim wshOutput As Worksheet
    Dim oCell As Range
    Dim lngRow As Long

    Set rngInput = Selection
    Set wshOutput = Worksheets.Add

    lngRow = 1
    wshOutput.Cells(lngRow, 1) = "Address"
    wshOutput.Cells(lngRow, 2) = "Comment"
    wshOutput.Range("A1:B1").Font.Bold = True

    For Each oCell In rngInput
    lngRow = lngRow + 1
    wshOutput.Cells(lngRow, 1) = oCell.Address(False, False)
    wshOutput.Cells(lngRow, 2) = "contains " & oCell.Value
    Next oCell

    Set oCell = Nothing
    Set wshOutput = Nothing
    Set rngInput = Nothing
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro structure (Excel XP)

    OK! I would put my test code, such as If oCell.value >161000#AND#<179000 Then with oCell.fill color = vb red .font color= vbwhite and so on, right after the For Each oCell In rngInput line. Then, I could put the message in the "Address" and "Comment field" as indicated. Is this correct logic? If it is, it would be a first, but am I on the right track? TYIA

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro structure (Excel XP)

    If you want to format the cell itself, yes. Use IntelliSense or the Object Browser to find out the correct names of properties:
    With oCell
    .Interior.Color = vbRed
    .Font.Color = vbWhite
    .BorderAround Weight:=xlThick, Color:=vbBlue
    End With

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro structure (Excel XP)

    Is my test correctly stated? If oCell.value >16000#AND#<=179999 ? That is the key question. Then... It's actually TWO ranges, fixed asset account ranges and depreciation account ranges 665000 and <=680000. That I think is the correct way to phrase it, but I'd like some confirm.
    thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro structure (Excel XP)

    oCell.value >16000#AND#<=179999 is not correct. You have to do it like this:<pre>If oCell.value > 16000 And oCell.Value <= 179999 Then</pre>

    I.e. don't use # symbols (they are for dates, e.g. today is #11/05/03#) and use complete expressions for each comparison.

Posting Permissions

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