Selecting Cells Dynamically in Different Rows

[alishehzad]

Hi Everyone,

I have started programming in Excel VB about a month ago. I guess I'm
doing well but Now I have a problem at hand.

My Macro runs in a loop and searches for multiple entries one by
one ... and needs to copy the required fields corresponding to that
Entry to another sheet.

The first column in my Sheet contains the reference Entries.

I search the Entire first column for that entry and if I find it then
I need to copy SOME of the cells of that Row to another Sheet.

I can get the number of that Row by ActiveCell.Row ... and I know
which colums of that Row I need to Copy.

But I DONT know how to use both of these to Select the Required
Cell ?????

Kindly guide me ... I will be really grateful for your help.

Thanks and Best Regards,

 

[Dave Mac]

Hi Everyone,

I have started programming in Excel VB about a month ago. I guess I'm
doing well but Now I have a problem at hand.

My Macro runs in a loop and searches for multiple entries one by
one ... and needs to copy the required fields corresponding to that
Entry to another sheet.

The first column in my Sheet contains the reference Entries.

I search the Entire first column for that entry and if I find it then
I need to copy SOME of the cells of that Row to another Sheet.

I can get the number of that Row by ActiveCell.Row ... and I know
which colums of that Row I need to Copy.

But I DONT know how to use both of these to Select the Required
Cell ?????

Kindly guide me ... I will be really grateful for your help.

Thanks and Best Regards,

Are you trying to find a list of unique entries?

 

[Guest]

Copy from Sheet1 to Sheet2 in next empty row

Sub FindAndCopy()
For rw = 1 To 100
If Sheets("Sheet1").Cells(rw, 1) = "What im looking at" Then
Sheets("Sheet1").Range("B" & rw).Copy Sheets("Sheet2").Range("A" &
Cells(65535, "A").End(xlUp).Row + 1)
Sheets("Sheet1").Range("D" & rw).Copy Sheets("Sheet2").Range("B" &
Cells(65535, "B").End(xlUp).Row + 1)
Sheets("Sheet1").Range("F" & rw).Copy Sheets("Sheet2").Range("E" &
Cells(65535, "E").End(xlUp).Row + 1)
End If
Next
End Sub


"Dave Mac" skrev:

 

[alishehzad]

No Dave ... im NOT trying to find unique entries.
And thanks Excellent for the help ....

My question is that:

Suppose I find my required data in Row 10. And i know that in Row 10 ,
I need to copy the Entries of Column F and Column R. ( which means
Cells F10 and R10)

How will i Select these Cells? Logically I could use

my_row = ActiveCell.Row
and then use Cells( "F" & my_row)
But this does not work.
Please tell me what do i need to write here. I think im only lacking
the correct syntax.

thanks a lot both of you...

Looking forward to your further guidance

 

[alishehzad]

Thanks a LOT Excelent...

Half of my problem is solved. By Looking at your example I found out
the way to select required cell in my required row.

------------- Code -------------------------------
my_row = ActiveCell.Row
Range("F" & my_row).Select
------------------------------------------------------
Works fine !!

Now for the other half...

Kindly tell me what should i do if I know the COLUMN and want to go to
a required Row ???

I have tried the following code but it does not work.

---------------------- Code ----------------------
my_col = ActiveCell.Column
Range( my_col & 19).Select
------------------------------------------------------

Perhaps this is because in this case the value stored in My_Col is not
the 'name' of the column ( like A , B or C) but the 'Number' of the
column ( i.e. 1, 2 or 3 instead of A , B or C).

Kindly guide me what to do ??

Thanks a lot for ure valuable help !

 

[Guest]

if i got ur right then try :

Sub FindAndCopy()
For rw = 1 To 100
If Sheets("Sheet1").Cells(rw, 1) = "What im looking at" Then
Sheets("Sheet1").Range("F" & rw).Copy Sheets("Sheet2").Range("A" &
Cells(65535, "A").End(xlUp).Row + 1) 'copy to colA
Sheets("Sheet1").Range("R" & rw).Copy Sheets("Sheet2").Range("B" &
Cells(65535, "B").End(xlUp).Row + 1) 'copy to colB
End If
Next
End Sub


"(e-mail address removed)" skrev:

 

[Dave Peterson]

Here are a few ways to address that range:

with activesheet

.cells(my_row,"F").copy _
destination:=....

.cells(my_row,"R").copy _
destination:=....

or
.Range("F" & my_row).copy _
destination:=....

.Range("R" & my_row).copy _
destination:=....

If you wanted to copy all the cells from F:R in my_Row, you could use:

.range(.cells(my_row,"F"),.cells(my_row,"R")).copy _
destination:=....

or
.cells(my_Row,"F").resize(1,13).copy _
destination:=....

or
.range("F" & my_Row).resize(1,13).copy _
destination:=....

end with

 

[Guest]

try
my_col = ActiveCell.Column
Cells(19, my_col).Select


"(e-mail address removed)" skrev:

 

[Guest]

or:
Range(Chr(my_col + 64) & 19).Select

"excelent" skrev:

 

[alishehzad]

THANKS A LOT BROTHERS !

Thanks a lot both of you.

This code did the trick

-------------------- Code ----------------
my_col = ActiveCell.Column
Cells(19, my_col).Select
----------------------------------------------


I know its very simple thing for people like you .... but as a
beginner i would have never solved this problem without your help !

You were a great help !!

Thanks a lot ... God Bless You !