1

I am attempting to create a macro to find values within a specified column and save their location for future use. The values to find are located on another sheet. The end goal is to be able to use the array full of locations to copy information from a few columns over onto a separate sheet.

Below is the code that creates my list....

Dim rng As Range
Dim TempSheet As Worksheet

'Copy list of Vendor IDs to be manipulated
sheets(4).Range("E4:E5000").Select
Set rng = Nothing
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeVisible)
rng.Copy
On Error GoTo 0
Set TempSheet = Sheets.Add

    TempSheet.Range("A1").Select
    Selection.PasteSpecial (xlValues)

'After Pasting values, change format to number format
[A:A].Select
With Selection
    .NumberFormat = "general"
    .Value = .Value
End With

'Remove duplicates from list
Range("A:A").RemoveDuplicates Columns:=1

Like I said....I dont even know where to start with this next portion of the coding...

kur ag
  • 591
  • 8
  • 19
Bakagami
  • 11
  • 3
  • 2
    One thing that may very well help you in the future... I was goign to comment on the use of `On Error Resume Next`, but realized it's tied to your use of `.select`... [do your best to avoid .select/.activate, where possible](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Related to the error handling, do your best to not *ignore* an error and code to handle it. – Cyril May 20 '19 at 18:43
  • Thank you very much for the pointer and the link to coding better practices. I'll do my best to understand it. – Bakagami May 20 '19 at 20:42

1 Answers1

0

You can use the find function to determine the column location; throw this into a loop of your search values:

k = .Rows(1).Find(What:=SEARCHVALUE, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Column
'may want to look at partials... verify that
'assumed searching in only row 1 for these "headers"

With your known destination, you can do use value=value such that:

wsd = sheets("destination")
wss = sheets("source")
wsd.columns(i).value = wss.columns(k).value
Cyril
  • 6,448
  • 1
  • 18
  • 31