| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheet referencing in a macro | Excel Programming | |||
| Sheet referencing in a macro | Excel Programming | |||
| Indirect Referencing in a Macro | Excel Programming | |||
| File referencing in macro | Excel Programming | |||
| macro relative referencing | Excel Discussion (Misc queries) | |||
Linear Mode
