• 2 small vba codes (excel 2002)

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » 2 small vba codes (excel 2002)

    Author
    Topic
    WSboat
    AskWoody Lounger
    November 3, 2004 at 4:51 pm #411773

    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.

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      November 4, 2004 at 9:32 am #895427

      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”), “*”

      Reply | Quote
      • WSboat
        AskWoody Lounger
        November 6, 2004 at 4:03 am #896361

        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.

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          November 7, 2004 at 11:01 am #896658

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

          Reply | Quote
          • WSboat
            AskWoody Lounger
            November 8, 2004 at 5:15 am #896861

            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.

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              November 8, 2004 at 8:03 am #896897

              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.

              Reply | Quote
            • WSboat
              AskWoody Lounger
              November 8, 2004 at 12:10 pm #896957

              ok. Hans.i realise now. Thanks.

              Reply | Quote
            • WSboat
              AskWoody Lounger
              November 8, 2004 at 12:10 pm #896958

              ok. Hans.i realise now. Thanks.

              Reply | Quote
            • WSboat
              AskWoody Lounger
              November 8, 2004 at 2:18 pm #896997

              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.

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              November 8, 2004 at 2:38 pm #897011

              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.

              Reply | Quote
            • WSboat
              AskWoody Lounger
              November 8, 2004 at 4:13 pm #897063

              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.

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              November 9, 2004 at 1:46 pm #897385

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

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              November 9, 2004 at 1:46 pm #897386

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

              Reply | Quote
            • WSboat
              AskWoody Lounger
              November 8, 2004 at 4:13 pm #897064

              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.

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              November 8, 2004 at 2:38 pm #897012

              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.

              Reply | Quote
            • WSboat
              AskWoody Lounger
              November 8, 2004 at 2:18 pm #896998

              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.

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              November 8, 2004 at 8:03 am #896898

              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.

              Reply | Quote
          • WSboat
            AskWoody Lounger
            November 8, 2004 at 5:15 am #896862

            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.

            Reply | Quote
        • WSHansV
          AskWoody Lounger
          November 7, 2004 at 11:01 am #896659

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

          Reply | Quote
      • WSboat
        AskWoody Lounger
        November 6, 2004 at 4:03 am #896362

        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.

        Reply | Quote
    • WSHansV
      AskWoody Lounger
      November 4, 2004 at 9:32 am #895428

      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”), “*”

      Reply | Quote
    Viewing 1 reply thread
    Reply To: 2 small vba codes (excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.