• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Macro to query with a txt file and make all words bold

Hi,

I have a txt file which has some name/numbers.I want a macro which can read the txt file search that text in the excel and if found make all the text bold and color to red.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 14
  • 13
1 Solution
 
hiteshgoldeneyeCommented:
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\mytext,txt" For Input As #FF
While EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If Sheet1.Cells(i, j) = str1 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
   
    Next
Wend
Close FF
End Sub

0
 
bsharathAuthor Commented:
Macro with no errors and no results too.

0
 
hiteshgoldeneyeCommented:
>Open "C:\mytext.txt" For Input As #FF

In this line you have to put your text file path
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bsharathAuthor Commented:
Put input as "#name"

Am i correct
every line shoiuld have #
0
 
hiteshgoldeneyeCommented:
I did not understand you, can you paste the macro u r using and the path where your text file is?
0
 
bsharathAuthor Commented:
This is the macro.

Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\mytext.txt" For Input As #FF
While EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If Sheet1.Cells(i, j) = str1 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
   
    Next
Wend
Close FF
End Sub

I have the txt file in C:\filename.txt
in the txt filei have

Sharath
Reddy
ramesh

And have Sharath
Reddy in the excel sheet 1.
0
 
hiteshgoldeneyeCommented:
In that case change your macro to

Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If Sheet1.Cells(i, j) = str1 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
   
    Next
Wend
Close FF
End Sub
0
 
hiteshgoldeneyeCommented:
Is it working fine now?
0
 
bsharathAuthor Commented:
I just checked.This is searching case sencitive.

I want the script to search even though the case is diffe.

Ex:
Sharath
sharath
SHARATH
SHarath
any thing it has to search

Will it search even though it is in any colum or row?
0
 
hiteshgoldeneyeCommented:
Then use the UCase function as shown
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If UCase(Sheet1.Cells(i, j)) = UCase(str1) Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
    Next
Wend
Close FF
End Sub
0
 
bsharathAuthor Commented:
I get this error.

Run-time error '13':
Type mismatch

If the names in the file is sharathreddy or Sharath reddy
and in the excel i just have it as sharath will it search the text?
0
 
hiteshgoldeneyeCommented:
The previous macro worked fine in my excel sheet
This is the macro which will search the text even if Sharath is in the excel file, and sharathreddy is in the text file
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If InStr(1, UCase(str1), UCase(Sheet1.Cells(i, j)), vbTextCompare) > 0 Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
    Next
Wend
Close FF
End Sub


0
 
bsharathAuthor Commented:
Can you send me a sample file
As the sheet name in my file is Rack servers
I changed the macro too. But get a compiling error
0
 
bsharathAuthor Commented:
Where should i run this
0
 
hiteshgoldeneyeCommented:
sample file io will not be able to upload from my office but this macro can be placed in the ThisWorkBook module which you can get after Pressing Alt+F11 in the workbook to open the macro editor and then run this macro.
0
 
bsharathAuthor Commented:
The sheet name is rack servers.
When i run i get no error or output.

I have the name Sharath in the txt file and even in the excel but no results
0
 
hiteshgoldeneyeCommented:
can you upload your file?
0
 
bsharathAuthor Commented:
0
 
hiteshgoldeneyeCommented:
buddy i dont have excel 2007 :(, only excel 2003. Cant open the file.
0
 
bsharathAuthor Commented:
0
 
hiteshgoldeneyeCommented:
i used ur file with the following macro it worked fine,
but the name sharath is not there in the file.
Sub Macro1()
Dim FF As Integer, str1 As String
FF = FreeFile
Open "C:\filename.txt" For Input As #FF
While Not EOF(FF)
    Line Input #FF, str1
    For i = 1 To Sheet1.UsedRange.Rows.Count
        For j = 1 To Sheet1.UsedRange.Columns.Count
           If InStr(1, UCase(str1), UCase(Sheet1.Cells(i, j)), vbTextCompare) > 0 And Sheet1.Cells(i, j) <> "" Then
                Sheet1.Cells(i, j).Font.Bold = True
                Sheet1.Cells(i, j).Interior.Color = RGB(255, 0, 0)
            End If
        Next
   
    Next
Wend
Close FF
End Sub

0
 
hiteshgoldeneyeCommented:
And sharath please put such questions in MS Excel Zone only
0
 
bsharathAuthor Commented:
Ok sure hitesh

I get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Run-time error '424':

Object required
---------------------------
OK   Help  
---------------------------

I just put name sharath in the excel sheet and ran the macro i got the error.
0
 
hiteshgoldeneyeCommented:
i put the name sharath in the sheet and did not get any error, in which cell did you put it?

do you know how to debug a macro?
0
 
bsharathAuthor Commented:
In colum 3 "C"
No i dont know to debug :<
0
 
bsharathAuthor Commented:
Should change the sheet name.Or the sheet name in the macro?
0
 
hiteshgoldeneyeCommented:
Yes try changing the sheetname and see the result
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now