Posts by Vky

Thanks.....

Quote from dangelor

Sorry for the late response...

Code

Sub SearchColB_v2()
    Dim rg As Range, i As Long, j As Long
    Dim v As Variant, v1 As Variant, v2 As Variant

    Set rg = Worksheets("Input").Cells(1, 1).CurrentRegion
    Set rg = rg.Offset(1).Resize(rg.Rows.Count - 1)
    
    v = rg
    v1 = Application.Transpose(rg.Columns(2).SpecialCells(xlCellTypeConstants))
    v2 = Application.Transpose(rg.Columns(1).SpecialCells(xlCellTypeConstants))
    For i = 1 To UBound(v1)
        v(i, 3) = "Not Found"
        For j = 1 To UBound(v2)
            If InStr(v1(i), v2(j)) > 0 Then
                v(i, 3) = v2(j)
            End If
        Next j
    Next i
    rg = v
End Sub

Display More

Thank You So Much Brother.....

Code Works perfectly......

Quote from dangelor

Possibly...

Code

Sub SearchColB()
    Dim v As Variant, i As Long, j As Long

    v = Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Offset(1)
    For i = 2 To UBound(v)
        If v(i, 1) <> vbNullString Then
            v(i, 3) = "Not Found"
            For j = 2 To UBound(v)
                If InStr(1, v(j, 2), v(i, 1), vbTextCompare) > 0 Then
                    v(i, 3) = v(i, 1)
                    Exit For
                End If
            Next j
        End If
    Next i
    Worksheets("Sheet1").Cells(1, 1).CurrentRegion = v
End Sub

Display More

Thank You for your time....!!

This code works good.... I need some changes in this code, can you help on this...

1. I want the matched output in C Column, upto last used row of B column.

In real time my input may go thousands of row.. if possible can u make it faster running time.....

Attached file four reference

Quote from royUK

You could do that with formulas. Attach an example workbook.

Thanks..

I have attached the Excel for your reference

Thanks..

I have attached the Excel for your reference

Hi Guys,

I need some help in VBA Macro, I have a list of names in Column A and B. I want to search() the Column A names to the entire range of Column B names. and need the matched names in the Column C.

Column B names are Exact or Partial names of column A.

Not Found in B ColumnJantonio

Quote from royUK

Are you doing this from Excel?

Attached my macro file for your reference.

When i click Add Text button it adds data in text file

Yes I am doing from excel... it is one part of my macro.... i have 2 lakh of rows in text file.

Hi, I am new to VBA. i need a help.

I have a more number of rows in Text file, just i want to open the text file from D drive, and insert the word "QQ" before and after each line of text file,

Example;

Procedure to export

Exporting data to a txt

Output should be

QQ Procedure to export QQ

QQ Exporting data to a txt QQ

I found the below code, it works fine, but it opens the .dat file and replaces it. but i need to directly open the text file and insert data.

Sub ADDTxt()
Dim s, s1 As String
Dim xx As String
s = "D:\Sample.txt"
s1 = "D:\Sample.dat"
Open s For Input As #1
Open s1 For Output As #2
While Not EOF(1)
    Line Input #1, xx
    Print #2, "QQ-" & xx & "QQ"
Wend
Close #1
Close #2
Kill s
Name s1 As s
End Sub

I have a input in Text file... i want to replace the word if it matches with excel file and replace it in text file....if the word finds in text file means it should replace that word with corresponding word.... i have placed the corresponding values in excel sheet... Pls help.. attached the sample file for your reference....

Quote from ArdusPetus

The output fields were blank because there is nothing to replace in the input field.

I modified the macro so that, in case no keyword was found in input, it prints input without changes in output field.

Replacement v1.1.xlsm

Thanks bro...!!!!!!!!!! Works perfectly.....

Thanks bro...!!!!!!!!!! Works perfectly.....

I find that, those rows are not replaced because of Case sensitive.... please remove Case sensitive bro.. Thanks....

Quote from ArdusPetus

Code

Option Explicit

Sub Replace()

Dim wOR As Worksheet, wRR As Worksheet
Dim rngIn As Range
Dim tabRepl As Variant
Dim i As Long
    Set wOR = Worksheets("Original Range")
Set wRR = Worksheets("Replace Range")

tabRepl = wRR.[A1].CurrentRegion

For Each rngIn In Range(wOR.[A2], wOR.Cells(Rows.Count, "A").End(xlUp))
    For i = LBound(tabRepl) To UBound(tabRepl)
        If InStr(1, rngIn, tabRepl(i, 1)) > 0 Then
            wOR.Cells(rngIn.Row, "C") = tabRepl(i, 1)
            wOR.Cells(rngIn.Row, "E") = tabRepl(i, 2)
            wOR.Cells(rngIn.Row, "G") = Strings.Replace(rngIn, tabRepl(i, 1), tabRepl(i, 2))
            Exit For
        End If
    Next i
Next rngIn
End Sub
[attach=1227660][/attach]

HTH,
-- 
AP

Display More

Thanks bro for the help.

Works partially...

i saw some of the rows are not replaced and Blanks .. Kindly check..... attached file for your reference.

Quote from ArdusPetus

Code

Option Explicit

Sub Replace()

Dim wOR As Worksheet, wRR As Worksheet
Dim rngIn As Range
Dim tabRepl As Variant
Dim i As Long
    Set wOR = Worksheets("Original Range")
Set wRR = Worksheets("Replace Range")

tabRepl = wRR.[A1].CurrentRegion

For Each rngIn In Range(wOR.[A2], wOR.Cells(Rows.Count, "A").End(xlUp))
    For i = LBound(tabRepl) To UBound(tabRepl)
        If InStr(1, rngIn, tabRepl(i, 1)) > 0 Then
            wOR.Cells(rngIn.Row, "C") = tabRepl(i, 1)
            wOR.Cells(rngIn.Row, "E") = tabRepl(i, 2)
            wOR.Cells(rngIn.Row, "G") = Strings.Replace(rngIn, tabRepl(i, 1), tabRepl(i, 2))
            Exit For
        End If
    Next i
Next rngIn
End Sub
[attach=1227660][/attach]

HTH,
-- 
AP

Display More

Thanks bro for the help.

Works partially...

i saw some of the rows are not replaced and Blanks .. Kindly check..... attached file for your reference.

i want output like this