2 small vba codes (excel 2002)

Topic

New Reply

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

Replies

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

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

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

Reply | Quote

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

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

ok. Hans.i realise now. Thanks.

Reply | Quote

ok. Hans.i realise now. Thanks.

Reply | Quote

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

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

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

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

Reply | Quote

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

Reply | Quote

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

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

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

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

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

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

Reply | Quote

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

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