PDA

View Full Version : [SOLVED] search excel cells for value and input corresponding value in adjecent cell



gint32
04-21-2016, 11:27 AM
i have a csv containing extracted data, I need a way of searching through an existing worksheet and populating the adjacent cells with the corresponding values from the csv file based on the corresponding values

ie : CSV

C1 smith
C4 Jones
D5 White


Worksheet Contents:

C1 smith
C2 N/A
C3 N/A
C4 Jones
D1 N/A
D2 N/A
D3 N/A
D4 N/A
D5 White

Any help will be much appreciated .

gint32
04-22-2016, 04:56 AM
Any idea out there, Maybe not enough info or ??? please tell me!

gint32
04-22-2016, 06:21 AM
i have a csv containing extracted data, I need a way of searching through an existing worksheet and populating the adjacent cells with the corresponding values from the csv file based on the corresponding values

ie : CSV

C1 smith
C4 Jones
D5 White


Worksheet Contents:

C1 smith
C2 N/A
C3 N/A
C4 Jones
D1 N/A
D2 N/A
D3 N/A
D4 N/A
D5 White

Any help will be much appreciated .

Worksheet contents before : update
A1 A2
C1 emptycell
C2 emptycell
C3 emptycell
C4 emptycell
C5 emptycell
C6 emptycell
C7 emptycell
C8 emptycell
D1 emptycell
D2 emptycell
D3 emptycell
D4 emptycell
D5 emptycell
D6 emptycell
D7 emptycell
D8 emptycell

MAX of sixteen changes if all populated,

heres what i so far:

doesn't accommodate for if not found go to next though:


Sub find()
'
' find Macro
' Macro recorded 4/22/2016 by gerry
'

'
Dim copied1, test1 As String
Dim myCells As Range



Worksheets("sheet2").Activate
Set myCells = Selection

'First find the value "C1" from new_CSV_file in Col A


Sheets("Sheet2").Select
Columns("A:A").Select

Selection.find(what:="c1", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
Set myCells = Selection

'Selection.Copy.Value
'Selection.Value.Copy
Selection.Copy


Sheets("Samplesheet").Select

'copied1 = Selection.Copy
Columns("A:A").Select
Selection.find(what:="c1", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Set myCells = Selection
'MsgBox myCells.Address

'Range myCells.Address.Select
'Range(myCells.Selection).PasteSpecial
ActiveSheet.Paste
'.Selection.Paste
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'next find copy and paste
'find C2

Sheets("Sheet2").Select
Columns("A:A").Select

Selection.find(what:="C2", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
Set myCells = Selection

'Selection.Copy.Value
'Selection.Value.Copy
Selection.Copy


Sheets("Samplesheet").Select

'copied1 = Selection.Copy
Columns("A:A").Select
Selection.find(what:="C2", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Set myCells = Selection
'MsgBox myCells.Address

'Range myCells.Address.Select
'Range(myCells.Selection).PasteSpecial
ActiveSheet.Paste
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Sheets("Sheet2").Select
Columns("A:A").Select

Selection.find(what:="C3", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
Set myCells = Selection

'Selection.Copy.Value
'Selection.Value.Copy
Selection.Copy


Sheets("Samplesheet").Select

'copied1 = Selection.Copy
Columns("A:A").Select
Selection.find(what:="C3", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Set myCells = Selection
'MsgBox myCells.Address

'Range myCells.Address.Select
'Range(myCells.Selection).PasteSpecial
ActiveSheet.Paste

End Sub

Here's my new long winded attempt with an on error goto ###:
code below, though it fails with message "object variable or with block variable not set" write at the 2nd "find" highlighted in bold, can anybody me help with this or is it to complicated for these forum members...I am starting to feel like i am talking to myself here.........................



Sub find2()
'
' find Macro
' Macro recorded 4/22/2016 by gerry
'

'
Dim copied1, test1 As String
Dim myCells As Range



Worksheets("sheet2").Activate
Set myCells = Selection

'First find the value "C1" from new_CSV_file in Col A


Sheets("Sheet2").Select
Columns("A:A").Select
On Error GoTo searchc2:
Selection.find(what:="c1", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
Set myCells = Selection

'Selection.Copy.Value
'Selection.Value.Copy
Selection.Copy


Sheets("Samplesheet").Select

'copied1 = Selection.Copy
Columns("A:A").Select
Selection.find(what:="c1", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
'Set myCells = Selection
'MsgBox myCells.Address

'Range myCells.Address.Select
'Range(myCells.Selection).PasteSpecial
ActiveSheet.Paste
'.Selection.Paste
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'next find copy and paste
'find C2
searchc2:

Sheets("Sheet2").Select
Columns("A:A").Select

On Error GoTo searchc3:
Selection.find(what:="C2", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
'Set myCells = Selection

'Selection.Copy.Value
'Selection.Value.Copy
Selection.Copy


Sheets("Samplesheet").Select

'copied1 = Selection.Copy
Columns("A:A").Select
Selection.find(what:="C2", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
Set myCells = Selection
'MsgBox myCells.Address

'Range myCells.Address.Select
'Range(myCells.Selection).PasteSpecial
ActiveSheet.Paste
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


searchc3:

Sheets("Sheet2").Select
Columns("A:A").Select
On Error GoTo searchend:
Selection.find(what:="C3", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(0, 1).Select
'Set myCells = Selection

'Selection.Copy.Value
'Selection.Value.Copy
Selection.Copy


Sheets("Samplesheet").Select

'copied1 = Selection.Copy
Columns("A:A").Select
Selection.find(what:="C3", After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
'Set myCells = Selection
'MsgBox myCells.Address

'Range myCells.Address.Select
'Range(myCells.Selection).PasteSpecial
ActiveSheet.Paste
searchend:
End Sub

gint32
04-22-2016, 03:02 PM
solved elsewhere as this forum is and was way to slow to answer

Aussiebear
04-22-2016, 05:18 PM
Did it cross your mind that it may have been due to the way the issue was presented?

gint32
04-23-2016, 05:37 AM
Did it cross your mind that it may have been due to the way the issue was presented?

Aussiebear, it never crossed my mind and the fact that nobody said anything yet answered heaps of other more complex questions I think was pretty S##T not a hey, how or even a give me more info, I think was tough to take!

Aussiebear
04-23-2016, 08:49 PM
As a fellow Australian, your attitude is embarrassing. Lets put that aside for the moment and understand the concept here at this forum. People contribute their time here freely, and are entirely undeserving of the rubbish you are purporting in Post #6. To gain a response to a thread, it needs to be constructed in such a way as to be interesting enough for people to want to read it, otherwise they will simply pass over it.

In post #1 you wrote "i have a csv containing extracted data, I need a way of searching through an existing worksheet and populating the adjacent cells with the corresponding values from the csv file based on the corresponding values". and then proceeded to give us some values. One has to be completely in the mood to chase down something as vague as this. So as I suggested in Post#5 your lack of a response is I believe entirely due to the way you constructed the thread, and the lack of a supporting workbook example.

Everyone thinks their thread is emotionally important, but you need to realise that its not to anyone else. So to be successful you need to show some humility to others, and not lash out with undeserving statements. I will await your response, but do urge you to be careful as to its intent.