Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
I did NOT open your attachment. Please do NOT attach files to the ng. You
MAY be invited to send a file directly to a respondent but do not attach to ng. The sub I sent you will do what you asked. suppose you have in col A A, col B A and col C C the sub will put C in the cell in col A so, your "selection" should be the col A range you want. for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1))) or for each c in range("a1:a"&range("a65536").end(xlup).row) "SolaSig AB" wrote in message ... After some Time is gone .... May it look like this one? Public strCnt As Integer Sub Chck4nms() Set strCnt = 1 /* debuger Ask heer for an Object! :( For Each c In ActiveCell.CurrentRegion.Cells If c = c.Offset(strCnt, 2) Then c.Offset(, 2) = c.Offset(strCnt, 3) Set strCnt = 1 ElseIf strCnt = 12 Then Set strCnt = 1 Next Else: strCnt = strCnt + 1 Next End Sub Please try. XLS is included on-Line "Don Guillett" wrote in message ... something like - untested for each c in selection if c=c.offset(,1)then c=c.offset(,2) next "SolaSig AB" wrote in message ... Hi All! Here is some Help asked, since I'm not familiar with Excel programming, I can't find an Answer for my problem. Here is the Problem: I need to replace come words in 1st.Cell with words from another 3rd.Cell if Content of the 1.st.Cell and the 2nd.Cells matches. Main problem is the Volume 25.000 Strings ? 15 Rows. As I understand the algorithm for this operation it may look like this: Get Ai Value Get Bj Value Compare Ai and Bj values for equality If true then get Cj Value and put to Ai Value; Next Ai (Ai++) If false then get next Bj (Bj++) Example: Before replacement A B C 1 RED RED R 2 BLUE BLUE B 3 BLUE 4 RED 5 BLUE after replacement A B C 1 R RED R 2 B BLUE B 3 B 4 R 5 B Thanks if there might be some ideas or help, On-Line |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
Dear Don,
Thanks but I actually didn't understand how to make this Sub. I have combined all your post and got following subs. But this don't work :( Here are two Subs, but I don't know what is wrong: Sub Chck() for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1)) if c=c.offset(,1)then c=c.offset(,2) next End Sub Sub Chck2() for each c in range("a1:a"&range("a65536").end(xlup).row) if c=c.offset(,1)then c=c.offset(,2) next End Sub As I see this Sub compares Values in Columns for same Row, but if cell will be in another row the Sub will not find any match. It may work for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
I guess I do NOT understand your problem.
"SolaSig AB" wrote in message ... Dear Don, Thanks but I actually didn't understand how to make this Sub. I have combined all your post and got following subs. But this don't work :( Here are two Subs, but I don't know what is wrong: Sub Chck() for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1)) if c=c.offset(,1)then c=c.offset(,2) next End Sub Sub Chck2() for each c in range("a1:a"&range("a65536").end(xlup).row) if c=c.offset(,1)then c=c.offset(,2) next End Sub As I see this Sub compares Values in Columns for same Row, but if cell will be in another row the Sub will not find any match. It may work for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
Dave, How is that materially different from my original?
for each c in selection if c=c.offset(,1)then c=c.offset(,2) next "Dave Peterson" wrote in message ... how about: Option Explicit Sub Chck() Dim c As Range With ActiveSheet For Each c In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Cells 'compare column A with the one to its right If LCase(c.Value) = LCase(c.Offset(0, 1).Value) Then 'if they were the same, then move '2 over (to the right) to column A. c.Value = c.Offset(0, 2).Value End If Next c End With End Sub SolaSig AB wrote: Dear Don, Thanks but I actually didn't understand how to make this Sub. I have combined all your post and got following subs. But this don't work :( Here are two Subs, but I don't know what is wrong: Sub Chck() for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1)) if c=c.offset(,1)then c=c.offset(,2) next End Sub Sub Chck2() for each c in range("a1:a"&range("a65536").end(xlup).row) if c=c.offset(,1)then c=c.offset(,2) next End Sub As I see this Sub compares Values in Columns for same Row, but if cell will be in another row the Sub will not find any match. It may work for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
You're right about that.
"Dave Peterson" wrote in message ... Only that it's contained in a complete macro (more than a snippet). From your previous reply, it sounded like another voice (however redundant) would help. Don Guillett wrote: Dave, How is that materially different from my original? for each c in selection if c=c.offset(,1)then c=c.offset(,2) next "Dave Peterson" wrote in message ... how about: Option Explicit Sub Chck() Dim c As Range With ActiveSheet For Each c In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Cells 'compare column A with the one to its right If LCase(c.Value) = LCase(c.Offset(0, 1).Value) Then 'if they were the same, then move '2 over (to the right) to column A. c.Value = c.Offset(0, 2).Value End If Next c End With End Sub SolaSig AB wrote: Dear Don, Thanks but I actually didn't understand how to make this Sub. I have combined all your post and got following subs. But this don't work :( Here are two Subs, but I don't know what is wrong: Sub Chck() for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1)) if c=c.offset(,1)then c=c.offset(,2) next End Sub Sub Chck2() for each c in range("a1:a"&range("a65536").end(xlup).row) if c=c.offset(,1)then c=c.offset(,2) next End Sub As I see this Sub compares Values in Columns for same Row, but if cell will be in another row the Sub will not find any match. It may work for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
The redundant part or the help part?
<vbg Don Guillett wrote: You're right about that. "Dave Peterson" wrote in message ... Only that it's contained in a complete macro (more than a snippet). From your previous reply, it sounded like another voice (however redundant) would help. <<snipped -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOWTO Replace from Row
Dave, thanks for your Help with whole Sub, and thanks to Don for original
idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed only if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single "A" column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row in 99,99% of cases. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B Dave thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single A column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R BLUE RED BLUE RED RED BLUE Have you any Idea how to make it work? Thanks again in advance for your patience and help! Andre |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorry, Mistake!
Oopps!
Wrote: It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in _C3_, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Have to write: It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in _C1_, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Andre "SolaSig AB" wrote in message ... Dave, thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed only if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single "A" column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row in 99,99% of cases. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B Dave thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single A column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R BLUE RED BLUE RED RED BLUE Have you any Idea how to make it work? Thanks again in advance for your patience and help! Andre |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorry, Mistake!
Real clear.
"SolaSig AB" wrote in message ... Oopps! Wrote: It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in _C3_, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Have to write: It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in _C1_, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Andre "SolaSig AB" wrote in message ... Dave, thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed only if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single "A" column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row in 99,99% of cases. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B Dave thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single A column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R BLUE RED BLUE RED RED BLUE Have you any Idea how to make it work? Thanks again in advance for your patience and help! Andre |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorry, Mistake!
I'm jumping in again!
For each cell in column A, check to see if matches a value in column B (any value--not just the same row!). And if it matches, then take the corresponding value from C (on the matched row and copy it to the cell in column A. A B C aaa aac x aab bbb y aac aaa z aad bbc w So for aaa, I'd see a match in row 3 and use Z in column A. For aac, I'd see a match in row 1 and use x in column A. A B C z aac x aab bbb y x aaa z aad bbc w If this is true, then I'm going to suggest a different approach. Insert a new column B (shifting B&C to C&D) Insert this formula in B1. =IF(ISERROR(VLOOKUP(A1,$C$1:$D$99,2,FALSE)),A1,VLO OKUP(A1,$C$1:$D$99,2,FALSE)) Adjust the ranges to match your data (and start in the correct row). If you change anything in column A, then column B will fix itself with the nex calculation. If you don't want to see column A, then you can hide it. If this were a one-time shot (you never need to do it again), then convert column B to values and delete column A. (one way to convert to values: copy|Paste special|Values) SolaSig AB wrote: Oopps! Wrote: It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in _C3_, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Have to write: It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in _C1_, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Andre "SolaSig AB" wrote in message ... Dave, thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed only if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single "A" column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row in 99,99% of cases. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B Dave thanks for your Help with whole Sub, and thanks to Don for original idea this really works! (Problem was as I see only in my poor knowledge of VBA) Now I'll try to make this Sub to work for whole B Column. As I see Value in Cell A will be changed if Value in Cell B matches, but the Row have to be the same also! It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed with Value in C3, but in real life there is not so simple because Value for A1 may match only Value in B3 and then Value in A1 have to be changed from C3 Value. Original text is much more complex there is 25.000 Rows and 15 Columns. (375 000 Cells) I will try to make all Columns separate, if this will work for a single column. In this single column data is been repeated some times so amount of cells in Single A column will be 25 000, and amount of Cells in B and C columns will be only about 5 000 cells per column, and cell's B Value does not correspond to A Value in current Row. As I already wrote on 23rd 08 2003: It works for RED RED R BLUE BLUE B But not for RED BLUE B BLUE RED R BLUE RED BLUE RED RED BLUE Have you any Idea how to make it work? Thanks again in advance for your patience and help! Andre -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HowTo: Baseball Calculator? | Excel Discussion (Misc queries) | |||
HOWTO DELETE UNLOCK CELLS | Excel Discussion (Misc queries) | |||
howto: concat (x1:x3) | New Users to Excel | |||
HowTo add row to series data | Charts and Charting in Excel | |||
HOWTO Replace from Row | Excel Programming |