Hi!

I need someone to explain to me the below vb code. What does it mean
in plain english.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

I don't understand the "IV". What does it means? I cannot find it i
the module.

Thank you.
Michae

--
Message posted from http://www.ExcelForum.com

if myrow were equal to 6, it would refer to cell IV6 which is the rightmost
cell in the worksheet. (column IV)

It then comes left until it finds the first cell not empty and goes to the
cell to the right of that (next empty cell in the row).

It then assigns a value to it.

--
Regards,
Tom Ogilvy

"Michael168 " wrote in message
...
Hi!

I need someone to explain to me the below vb code. What does it means
in plain english.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

I don't understand the "IV". What does it means? I cannot find it in
the module.

Thank you.
Michael


---
Message posted from http://www.ExcelForum.com/

Hi Tom Ogilvy,

Thanks for explaining. How do I change the starting columns to "Col K
instead of the next empty cell to the right? I means fix it to start a
column "K".

=======
Old code #

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value
=======

Regards,
Michael

Tom Ogilvy wrote:
[b]if myrow were equal to 6, it would refer to cell IV6 which is th
rightmost
cell in the worksheet. (column IV)

It then comes left until it finds the first cell not empty and goe
to the
cell to the right of that (next empty cell in the row).

It then assigns a value to it.

--
Regards,
Tom Ogilv

--
Message posted from http://www.ExcelForum.com

Hi Michael,

. How do I change the starting columns to "Col K"
instead of the next empty cell to the right? I means fix it to start at
column "K".

=======
Old code #

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value
=======

Try:

Range("K" & myRow).Value = myRange.Cells(i, j).Value


---
Regards,
Norman



"

Hi Norman Jones,

The code don't work right. It start at col "K" but stop writing th
other values to the other columns.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

The above code is working except it always start next to empty cell
What I want how to specify it to start in col "K". The rest remai
unchange.

I even change the code to

Range("K" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

This also don't work. It still start next to the first empty cell.
actually need it to write in the values from Col "K" to Col "R".

Regards,
Michae

--
Message posted from http://www.ExcelForum.com

Hi Michael,

If (and it is a big if) I correctly interpret your requirements, try:

Range("K" & myRow).End(xlToLeft)(1, 0).Value = _
myRange.Cells(i,
j).Value

---
Regards,
Norman



"Michael168 " wrote in message
...
Hi Norman Jones,

The code don't work right. It start at col "K" but stop writing the
other values to the other columns.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

The above code is working except it always start next to empty cell.
What I want how to specify it to start in col "K". The rest remain
unchange.

I even change the code to

Range("K" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

This also don't work. It still start next to the first empty cell. I
actually need it to write in the values from Col "K" to Col "R".

Regards,
Michael


---
Message posted from http://www.ExcelForum.com/

Without knowing what your sheet looks like

if isempty(Range("K" & myRow)) then
Range("K" & myRow).Value = myRange.Cells(i,j).Value
Range("L" & myRow).Resize(1,7).ClearContents
else
Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value
End if

this should work if it was going farther to the left than column K.

--
Regards,
Tom Ogilvy


"Michael168 " wrote in message
...
Hi Norman Jones,

The code don't work right. It start at col "K" but stop writing the
other values to the other columns.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

The above code is working except it always start next to empty cell.
What I want how to specify it to start in col "K". The rest remain
unchange.

I even change the code to

Range("K" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

This also don't work. It still start next to the first empty cell. I
actually need it to write in the values from Col "K" to Col "R".

Regards,
Michael


---
Message posted from http://www.ExcelForum.com/

Hi! Tom Ogilvy,

Thanks for the code. Yes,it works perfectly.

One more question.

My module works from bottom up. Columns "K" to "R" store numeri
values.
How do I add in one condition to check for column "K"? When the cod
encounters column "K" I want it to check if column "K" is empty. If i
is not empty ,I want it to exit the sub and give a message "Done
otherwise keep processing until column "K" is not empty.

Regards,
Michael

Tom Ogilvy wrote:
*Without knowing what your sheet looks like

if isempty(Range("K" & myRow)) then
Range("K" & myRow).Value = myRange.Cells(i,j).Value
Range("L" & myRow).Resize(1,7).ClearContents
else
Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value
End if

this should work if it was going farther to the left than column K.

--
Regards,
Tom Ogilvy


"Michael168 " wrote i
message
...
Hi Norman Jones,

The code don't work right. It start at col "K" but stop writin
the
other values to the other columns.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

The above code is working except it always start next to empt
cell.
What I want how to specify it to start in col "K". The rest remain
unchange.

I even change the code to

Range("K" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

This also don't work. It still start next to the first empty cell
I
actually need it to write in the values from Col "K" to Col "R".

Regards,
Michael


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com

The only thing my code did differently was to start in column K if it was
empty. But you are saying you want to stop processing if column K is
empty - so I am confused. Perhaps you could show a bit more of your code -
if you are looping backwards and always will fill in values in columns K to
R, there may be a way to do it like

for ii = lastrow to 1 step -1
if isempty(Range("K" & i)) then exit for
Range("K" & i).Resize(1, 8).Value = _
myRange.Cells(i,1).Resize(1,8).Value
end if
Next

--
Regards,
Tom Ogilvy




"Michael168 " wrote in message
...
Hi! Tom Ogilvy,

Thanks for the code. Yes,it works perfectly.

One more question.

My module works from bottom up. Columns "K" to "R" store numeric
values.
How do I add in one condition to check for column "K"? When the code
encounters column "K" I want it to check if column "K" is empty. If it
is not empty ,I want it to exit the sub and give a message "Done"
otherwise keep processing until column "K" is not empty.

Regards,
Michael

Tom Ogilvy wrote:
*Without knowing what your sheet looks like

if isempty(Range("K" & myRow)) then
Range("K" & myRow).Value = myRange.Cells(i,j).Value
Range("L" & myRow).Resize(1,7).ClearContents
else
Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value
End if

this should work if it was going farther to the left than column K.

--
Regards,
Tom Ogilvy


"Michael168 " wrote in
message
...
Hi Norman Jones,

The code don't work right. It start at col "K" but stop writing
the
other values to the other columns.

Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

The above code is working except it always start next to empty
cell.
What I want how to specify it to start in col "K". The rest remain
unchange.

I even change the code to

Range("K" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value

This also don't work. It still start next to the first empty cell.
I
actually need it to write in the values from Col "K" to Col "R".

Regards,
Michael


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

Hi Tom Ogilvy,

No, what I want is to stop processing if column K is "NOT" empty.

Anyway please kindly take a look at the module below.

The module start from the last row, read "Col D & Col E" then fill the
values into "Col K" to "Col R".

I need to add in one condition i.e. whenever it encounters the first
"Col K" is "NOT EMPTY" then "EXIT THE SUB" otherwise continue to
process until the first "Col K" is "NOT EMPTY". Many rows of datas are
add in everyday to "Col D & Col E". So to speed up the updating process
I just need to update "Col K" to "Col R" that have not been updated
instead of running the code from the last row until the first row for
every execution.

I hope I have clearly explained. Please kindly add in or modify the
code.

SUB MOVEDATA()

DIM MYRANGE AS RANGE
DIM MYROW AS LONG
DIM I AS INTEGER
DIM J AS INTEGER
DIM MYCNT AS INTEGER

WITH ACTIVESHEET.USEDRANGE
MYROW = .ROWS(.ROWS.COUNT).ROW
END WITH

WHILE MYROW 0
MYCNT = 0
SET MYRANGE = RANGE(\"D\" & MYROW & \":E\" & MYROW)
FOR I = 1 TO -MYROW + 2 STEP -1
FOR J = 1 TO 2
IF APPLICATION.COUNTIF(RANGE(\"K\" & MYROW & \":R\" & MYROW), _
MYRANGE.CELLS(I, J).VALUE) = 0 THEN
IF ISEMPTY(RANGE(\"K\" & MYROW)) THEN
RANGE(\"K\" & MYROW).VALUE = MYRANGE.CELLS(I, J).VALUE
RANGE(\"L\" & MYROW).RESIZE(1, 7).CLEARCONTENTS
ELSE
RANGE(\"IV\" & MYROW).END(XLTOLEFT)(1, 2).VALUE = _
MYRANGE.CELLS(I, J).VALUE
END IF
MYCNT = MYCNT + 1
IF MYCNT = 8 THEN GOTO FOUND8:
END IF
NEXT J
NEXT I
FOUND8:
MYROW = MYROW - 1
WEND

END SUB


Regards,
Michael


---
Message posted from http://www.ExcelForum.com/