Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 small vba codes (excel 2002)

    hi. i would like to create 2 codes here. one is IF cell II4>II8, then the active cell is in IE1 if not place the active cell in IC11. another code is place the active cell below a cell that has * mark (or any mark i can modify mine subject to vba code convenient) in a range of cell given . that's all. Please help. Thanks.

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

    Re: 2 small vba codes (excel 2002)

    First question:

    Sub SelectACell()
    If Range("II4") > Range("II8") Then
    Range("IE1").Select
    Else
    Range("IC11").Select
    End If
    End Sub

    Second question:

    Sub SelectBelowMark(oRange As Range, strMark As String)
    Dim oCell As Range
    Set oCell = oRange.Find(What:="~" & strMark)
    If Not oCell Is Nothing Then
    oCell.Offset(1, 0).Select
    End If
    End Sub

    Call it like this:

    SelectBelowMark Range("A1:E4"), "*"

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 small vba codes (excel 2002)

    hi Hans, the first solution works perfect. THANKS. i don't know how to use the second code, about this code,
    Sub SelectBelowMark(oRange As Range, strMark As String)
    Dim oCell As Range
    Set oCell = oRange.Find(What:="~" & strMark)
    If Not oCell Is Nothing Then
    oCell.Offset(1, 0).Select
    End If
    End Sub


    where to put the SelectBelowMark Range("A1:E4"), "*" in your code, or to make it run??
    sorry for my illiterate.

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

    Re: 2 small vba codes (excel 2002)

    You'll have to tell us how and where you want to use the code.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 small vba codes (excel 2002)

    ok. the range to which to find the * mark is A11:Z11. the active cell to which the final result would be, will be below one of the cell in A11:Z11 depend on where the * mark is located in one of the cell in A11:Z11. Thanks.

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

    Re: 2 small vba codes (excel 2002)

    Make sure that you have copied the SelectBelowMark procedure into a module. Then enter the following code into the same module:

    Sub SelectNow()
    SelectBelowMark Range("A11:Z11"), "*"
    End Sub

    When you run this macro, it will select the cell below the one in A11:Z11 that contains an asterisk *. If you want to select a cell below a cell in B10:N10 that contains an @, use

    Sub SelectNow()
    SelectBelowMark Range("B10:N10"), "@"
    End Sub

    etc.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 small vba codes (excel 2002)

    ok. Hans.i realise now. Thanks.

  8. #8
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    deleting & placing new mark

    hi. this macro is i think extension of the code you provided above although i use it differently. Thanks. because i will run another macro after above code. and this will it after that macro. can you help me here??,, Thanks.

    Create a new macro, of 4 cells involved, no matter where they are: on an active cell, go up one cell, then delete the * mark (or whatever mark, use * in this e.g.) go one cell to the right, place the same mark (place * mark there) and finally the active cell is below the new * mark. i tried record macro but that will remain fix selected range and my intention is to make it placing new mark to the right of original mark and deleting original mark whenever there is a mark exists within a range. Thank you.

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

    Re: deleting & placing new mark

    Try this:

    Sub MoveToTheRight()
    With ActiveCell
    .Offset(-1, 1) = .Offset(-1, 0)
    .Offset(-1, 0).ClearContents
    .Offset(0, 1).Select
    End With
    End Sub

    If you follow what this macro does, you will learn how to work with relative offsets to a cell (in this case, to the active cell). The two arguments to Offset are the RowOffset first, then the ColumnOffset.

  10. #10
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting & placing new mark

    Hi Hans. Thanks for your code and quick reply. i learn that it is clearcut to follow two items below about the 2 arguments of Offset: RowOffset first, then the ColumnOffset.

    .Offset(-1, 0).ClearContents
    .Offset(0, 1).Select

    but not this one:
    .Offset(-1, 1) = .Offset(-1, 0)
    that will shift the * mark from original place to right and place active cell in its original active cell.
    but having known this function is good as well for my other related purpose maybe. Thanks again.

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

    Re: deleting & placing new mark

    I don't understand what you're saying here, but since your problem seems to be solved, never mind.

Posting Permissions

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