referencing a cell in a macro
 

Hi all,

I'm trying to write a macro that looks at the entry in A1, and if this is
11, then it copies the entry in B1 into another sheet.
But then it loops through all the rows with data in them, so I can't just
use "A1 and "B1".

At the moment, I'm using "MyCell", as per the code below. But what I'd like
to do is change the line
Range("B4").Select
to something like
Range(Cells(MyCell.Row, MyCell.Column)).Select

I know this isn't the right way to reference it, anyone know how?
NB: I'm using Excel 2003, so I don't have access to the OFFSET command.

Thanks in advance! :)
Rebecca



For Each MyCell In Selection
If MyCell.Value Like 11 Then
' Change to follow cells properly
Range("B4").Select
Selection.Copy
Sheets("11 out").Select
ActiveSheet.Paste
End If
Next

End Sub

referencing a cell in a macro
 

Hi Rebecca

Try something like the following

Sub test()
Dim c As Range, i As Long
Dim wsd As Worksheet
Set wsd = ThisWorkbook.Sheets("11 Out")
i = wsd.Cells(Rows.Count, 2).End(xlUp).Row + 1

For Each c In Selection
If c.Value = 11 Then
c.Offset(0, 1).Copy wsd.Cells(i, 2)
i = i + 1
End If
Next

End Sub



--
Regards

Roger Govier


"R Dunn" wrote in message
...
Hi all,

I'm trying to write a macro that looks at the entry in A1, and if this
is
11, then it copies the entry in B1 into another sheet.
But then it loops through all the rows with data in them, so I can't
just
use "A1 and "B1".

At the moment, I'm using "MyCell", as per the code below. But what I'd
like
to do is change the line
Range("B4").Select
to something like
Range(Cells(MyCell.Row, MyCell.Column)).Select

I know this isn't the right way to reference it, anyone know how?
NB: I'm using Excel 2003, so I don't have access to the OFFSET
command.

Thanks in advance! :)
Rebecca



For Each MyCell In Selection
If MyCell.Value Like 11 Then
' Change to follow cells properly
Range("B4").Select
Selection.Copy
Sheets("11 out").Select
ActiveSheet.Paste
End If
Next

End Sub

referencing a cell in a macro
 

Hi Rebecca,

Cells(MyCell.Row, MyCell.Column).Select

will do just that. Cells is a Range, which you can select.

You can also use
r = MyCell.Row
c = MyCell.Column
Cells(r,c).Select
and then use r = r + 1 to reference the cell below.

Hope this helps.




--
Gerd


"R Dunn" wrote:

Hi all,

I'm trying to write a macro that looks at the entry in A1, and if this is
11, then it copies the entry in B1 into another sheet.
But then it loops through all the rows with data in them, so I can't just
use "A1 and "B1".

At the moment, I'm using "MyCell", as per the code below. But what I'd like
to do is change the line
Range("B4").Select
to something like
Range(Cells(MyCell.Row, MyCell.Column)).Select

I know this isn't the right way to reference it, anyone know how?
NB: I'm using Excel 2003, so I don't have access to the OFFSET command.

Thanks in advance! :)
Rebecca



For Each MyCell In Selection
If MyCell.Value Like 11 Then
' Change to follow cells properly
Range("B4").Select
Selection.Copy
Sheets("11 out").Select
ActiveSheet.Paste
End If
Next

End Sub

referencing a cell in a macro
 

Thanks, Gerd. I didn't realise that "Cells" was already a range, so I was
using "Range(Cells(row, col))" - oops!


"gerdmain" wrote:

Hi Rebecca,

Cells(MyCell.Row, MyCell.Column).Select

will do just that. Cells is a Range, which you can select.

You can also use
r = MyCell.Row
c = MyCell.Column
Cells(r,c).Select
and then use r = r + 1 to reference the cell below.

Hope this helps.




--
Gerd


"R Dunn" wrote:

Hi all,

I'm trying to write a macro that looks at the entry in A1, and if this is
11, then it copies the entry in B1 into another sheet.
But then it loops through all the rows with data in them, so I can't just
use "A1 and "B1".

At the moment, I'm using "MyCell", as per the code below. But what I'd like
to do is change the line
Range("B4").Select
to something like
Range(Cells(MyCell.Row, MyCell.Column)).Select

I know this isn't the right way to reference it, anyone know how?
NB: I'm using Excel 2003, so I don't have access to the OFFSET command.

Thanks in advance! :)
Rebecca



For Each MyCell In Selection
If MyCell.Value Like 11 Then
' Change to follow cells properly
Range("B4").Select
Selection.Copy
Sheets("11 out").Select
ActiveSheet.Paste
End If
Next

End Sub

referencing a cell in a macro
 

Thanks for the code, Roger.
I can't use offset, 'cos I'm using excel 2003, but I'll just use Cells
(MyCell, MyCell + 1) instead.
The rest looks useful though

"Roger Govier" wrote:

Hi Rebecca

Try something like the following

Sub test()
Dim c As Range, i As Long
Dim wsd As Worksheet
Set wsd = ThisWorkbook.Sheets("11 Out")
i = wsd.Cells(Rows.Count, 2).End(xlUp).Row + 1

For Each c In Selection
If c.Value = 11 Then
c.Offset(0, 1).Copy wsd.Cells(i, 2)
i = i + 1
End If
Next

End Sub



--
Regards

Roger Govier


"R Dunn" wrote in message
...
Hi all,

I'm trying to write a macro that looks at the entry in A1, and if this
is
11, then it copies the entry in B1 into another sheet.
But then it loops through all the rows with data in them, so I can't
just
use "A1 and "B1".

At the moment, I'm using "MyCell", as per the code below. But what I'd
like
to do is change the line
Range("B4").Select
to something like
Range(Cells(MyCell.Row, MyCell.Column)).Select

I know this isn't the right way to reference it, anyone know how?
NB: I'm using Excel 2003, so I don't have access to the OFFSET
command.

Thanks in advance! :)
Rebecca



For Each MyCell In Selection
If MyCell.Value Like 11 Then
' Change to follow cells properly
Range("B4").Select
Selection.Copy
Sheets("11 out").Select
ActiveSheet.Paste
End If
Next

End Sub

referencing a cell in a macro
 

Hi Rebecca
I can't use offset, 'cos I'm using excel 2003
I don't understand
Offset works in XL2003 absolutely fine.
I wrote the code with Xl2003 and tested before posting.

--
Regards

Roger Govier


"R Dunn" wrote in message
...
Thanks for the code, Roger.
I can't use offset, 'cos I'm using excel 2003, but I'll just use Cells
(MyCell, MyCell + 1) instead.
The rest looks useful though

"Roger Govier" wrote:

Hi Rebecca

Try something like the following

Sub test()
Dim c As Range, i As Long
Dim wsd As Worksheet
Set wsd = ThisWorkbook.Sheets("11 Out")
i = wsd.Cells(Rows.Count, 2).End(xlUp).Row + 1

For Each c In Selection
If c.Value = 11 Then
c.Offset(0, 1).Copy wsd.Cells(i, 2)
i = i + 1
End If
Next

End Sub



--
Regards

Roger Govier


"R Dunn" wrote in message
...
Hi all,

I'm trying to write a macro that looks at the entry in A1, and if
this
is
11, then it copies the entry in B1 into another sheet.
But then it loops through all the rows with data in them, so I
can't
just
use "A1 and "B1".

At the moment, I'm using "MyCell", as per the code below. But what
I'd
like
to do is change the line
Range("B4").Select
to something like
Range(Cells(MyCell.Row, MyCell.Column)).Select

I know this isn't the right way to reference it, anyone know how?
NB: I'm using Excel 2003, so I don't have access to the OFFSET
command.

Thanks in advance! :)
Rebecca



For Each MyCell In Selection
If MyCell.Value Like 11 Then
' Change to follow cells properly
Range("B4").Select
Selection.Copy
Sheets("11 out").Select
ActiveSheet.Paste
End If
Next

End Sub