cell movement

[mani_singh]

hi everyone, i need the command to move / highlight cells in a macro i.e. how to make it highlight the cell to the left, right, top and bottom.

and if you know how to loop the comand in the macro untill it does find any more data in the list pleaselet me know

thanks in advance!

 

[Joe Was]

More Info Please?

OffSet(Row, Col)

 

[mani_singh]

left by one cell , right by one, up by one and down by one please.

what it needs to do at the end is read a cell run a nested if statement if true copy the required 5 cells from that row into another sheet and drop down a row and move across to the next reference cell directly below the first (same event if the if statement was false) and run the nested if again on that row untill it drops a row and finds no data!

thank you!

 

[Joe Was]

Sorry don't have a clue what you are wanting!
Not enough information.
What do you have now, what data, what cells?
What do you want to end up with, and where?
Do you want VBA code or Sheet Formulas?

 

[mani_singh]

no probs what i have i'll show you!

ok what i need to do is this

two sheets 1st (shift left data) is for data and calculations, 2nd (shift left abuse) is for results.

on sheet 1 - (stating from row 9) cols m & n have ref data in them, i need to run an if statement asking if the val in n is larger than in m. if yes copy cells o9,p9,q9,r9,s9 and paste them into sheet 2 from col a row 2.

Once done OR if false ignore that row, go to the next row and run again untill all rows which return TRUE from the if statement have been copied across to sheet 2.

Untill the macro loop finds empty data in col n and stops


HOPE I MADE A LITTLE MORE SENSE THAT TIME and thanks any help you can give me.

 

[Joe Was]

Try this:


Sub myTestThenMove()
'Standard module code, like: Module1.
Dim r As Object
Dim n&

Application.ScreenUpdating = False
Worksheets("Sheet1").Select

For Each r In Worksheets("Sheet1").UsedRange.Rows
n = r.Row

If Worksheets("Sheet1").Cells(n, 14) > Worksheets("Sheet1").Cells(n, 13) Then
Worksheets("Sheet1").Range(Cells(n, 15), Cells(n, 19)).Copy _
Destination:=Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
Else
End If
Next r

Application.CutCopyMode = True
Application.ScreenUpdating = True
End Sub

 

[mani_singh]

ive put it in and its sort of working, its begining at row 8 not nine but is only bringing out one row onto the other sheet not repeating the process for the other rows on sheet 1

it must continue extracting the data untill it sees nothing in sheet 1 col n

 

[mani_singh]

i.e. gets to the bottom row (empty col n)

 

[Joe Was]

I used:

Each r In Worksheets("Sheet1").UsedRange.Rows

"UsedRange" which works for full worksheet ranges, if you have blank rows and columns between some data then this does not work. It needs to be converted to a defined working range

Like:

myBotR = Sheets("Sheet1").Range("N65536").End(xlUp).Row

myRng = Sheets("Sheet1").Range(Cells(1, 1), Cells(myBotR, 19)

 

[mani_singh]

thanks for that joe, got that sorted now!