VBA for Text to Column and Vlookup and Concatenate

[Akshay_divecha]

Dear Experts,

Not able to add an attachment.

Working on a code that can fetch file name from local drive and rename it.

Have break inrto 3 codes.

1. Fetching file name
2. Getting the new file name
3. Renaming in local drive.

Macro 1 & 3 are working fine but facing issues with macro 2.

I need to do below things,
1. copy data from column A to column B (not first row but from 2 row till the last row)
2. on column B - Text to column, delimited, other, -
3. on column C - vlookup the value in worksheet FDR150 column "AF :AJ" and fetch the value of "AJ"
4. on column D - vlookup the value in "C" in worksheet location codes column " B:C" and fetch the value on "C"
5. on column E - Concatenate "column D" "space" "column A"

Here is the code but it is limited to 200 rows i am looking to get the details till the last updated row.

Please help.

Sub GetFileName()
'
' GetFileName Macro
'

'
    range("A2").Select
    range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:B").Select
    range("B2").Activate
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    ActiveWindow.SmallScroll Down:=-3
    range("C2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'FDR150'!C[29]:C[33],5,0)"
    range("D2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Nhava Sheva Location Codes'!C[-2]:C[-1],2,0)"
    range("E2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-4])"
    range("C2:E2").Select
    Selection.Copy
    range("B2").Select
    Selection.End(xlDown).Select
    range("C382").Select
    range(Selection, Selection.End(xlUp)).Select
    range("C3:C382").Select
    range("C382").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.ScrollRow = 361
    ActiveWindow.ScrollRow = 358
    ActiveWindow.ScrollRow = 278
    ActiveWindow.ScrollRow = 199
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 1
    range("E2").Select
End Sub

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...xt-to-column-and-vlookup-and-concatenate.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

How about

Sub GetFileName()
    
   Dim usdrws As Long
   
   usdrws = Range("A" & Rows.Count).End(xlUp).Row
   
   Range("B2:B" & usdrws).FormulaR1C1 = "=LEFT(rc[-1],FIND(""-"",rc[-1])-1)+0"
   Range("C2:C" & usdrws).FormulaR1C1 = "=VLOOKUP(RC[-1],'FDR150'!C[29]:C[33],5,0)"
   Range("D2:D" & usdrws).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Location Codes'!C[-2]:C[-1],2,0)"
    Range("E2:E" & usdrws).FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-4])"
End Sub

 

[Akshay_divecha]

Dear Fluff,

Thanks.. it worked, going ahead tried adding one more criteria in the current code but failed.

can you please help one more time.

in column E - find the value mention in "column B" in worksheet FDR150 columns AF:AK and if the text in column AK is "rubber", "akshay" , "boat" , "house" then column E to be updated as "F"

in column F = If column E= F then concatenate "column F" "space" "column D" "space" "column A" and if not then concatenate "column D" "space" "column A"

 

[Fluff]

There are no values in the workbook that you originally supplied in ExcelForum that have those values.
So I'm not sure what you're looking for.

 

[Akshay_divecha]

Dear Fluff,

Do you mean the workbook attached in https://www.excelforum.com/excel-pr...xt-to-column-and-vlookup-and-concatenate.html

if Yes.. that contains workbook named FDR150 and text to be found in column AK is "LORDS" "FREIGHT" "NEWPORT" etc....

 

[Fluff]

Initially you said

if the text in column AK is "rubber", "akshay" , "boat" , "house"

now you are saying

"LORDS" "FREIGHT" "NEWPORT" etc....

Which is correct?
Also what does etc... mean?
You mention various columns but do not state on which sheet.
Please supply a concise, but accurate description of what you need.

 

[Akshay_divecha]

Dear Fluff,

Apology for creating a confusing.

correct text which in need to find in column AK of worksheet "FDR150" is "LORDS" "FREIGHT" "NEWPORT".... (text can be further added)

etc means... i need to add more text this this string...

I need to sort such customers and need to process additional task of sending mail.. for which have another macro which can send mail in bulk.

I need to do below things,
1. copy data from column A to column B (not first row but from 2 row till the last row)
2. on column B - Text to column, delimited, other, -
3. on column C - vlookup the value in worksheet FDR150 column "AF :AJ" and fetch the value of "AJ"
4. on column D - vlookup the value in "C" in worksheet location codes column " B:C" and fetch the value on "C"
5. on column E - lookup for the value mention in "column B" in worksheet FDR150 columns AF:AK and if the text in column AK is "LORDS" "FREIGHT" "NEWPORT" (can be added further) then column E to be updated as "F"
6. on column F - If column E= F then concatenate "column F" "space" "column D" "space" "column A" and if not then concatenate "column D" "space" "column A"

 

[Fluff]

Points 1 to 4 have already been done.
5) Which sheet are cols E & F on?
6) Which sheets are cols A,D,E & F on?

 

[Akshay_divecha]

5. on Column E.. worksheet "Rename" lookup for the value mention in "column B" in worksheet FDR150 columns AF:AK and if the text in column AK is "LORDS" "FREIGHT" "NEWPORT" (can be added further) then column E to be updated as text "F" OR "FWD"
6. on column F.. . worksheet "Rename" = If column E= "F" or "FWD" then concatenate "column E" "space" "column D" "space" "column A" and if not then concatenate "column D" "space" "column A"