Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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 " |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Question
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code question | Excel Discussion (Misc queries) | |||
VBA code question | Excel Discussion (Misc queries) | |||
code question | Excel Discussion (Misc queries) | |||
Another code question | Excel Discussion (Misc queries) | |||
VB Code Question | Excel Discussion (Misc queries) |