Macro help - search for text in Excel

 tg -

I've been trying to wrap my head around how to do this but haven't had any luck -- I want to search for text in excel in a spreadsheet of 4000 it'll save a ton of time.

Here's the situation I want solved.

I have an array of 22 columns (AI:BD) that is mostly blank (formulas returning text of "") and occasionally 1-2 (identical) text inputs per row. What I want to do is search this pretty much blank array for the account code (a number) and put it in one column (AH) on the same row. How do I do this?

4 replies

Option Explicit

Sub GLLoadDataGenerator()

'Write GL Test Data to a tab delimited .TXT file

Dim NumberofRecords As Variant, i As Variant
Dim myPath As String, myFile As String, DateTime As String, FolderName As String
Dim iTEXT As Integer
Dim sTEXT As String 'csv string to print
Dim sDelimiter As String
Dim FileNum As Integer
Dim fso
Dim varglcode As Variant, strgltext As String, varbalance As Variant, strgltypecode As Variant, varfundcode As Variant, varclasscode As Variant, dtloaddate As Date


sDelimiter = vbTab
varglcode = 100000
varbalance = 1000000
NumberofRecords = 50000

'DateTime = Format(Date, "dd-mm-yyyy") + "_" + Format(Time, "hh-mm-ss AM/PM")
FileNum = FreeFile
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop") & "\TestLoadData\"
'myFile = "GLLoadTestData_" & NumberofRecords & "_" & DateTime & ".txt"
myFile = "GLLoadTestData_" & NumberofRecords & " Records" & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")

If Not fso.FolderExists(myPath) Then
fso.CreateFolder (myPath)
End If

Open myPath & myFile For Output Access Write As #FileNum
'Print the header row
Print #FileNum, "glcode" & sDelimiter & "gltext" & sDelimiter & "balance" & sDelimiter & "gltypecode" & sDelimiter & "fundcode" & sDelimiter & "classcode" & sDelimiter & "loaddate"

For i = 1 To NumberofRecords
'Generate the GL Account Code
varglcode = varglcode + 1
sTEXT = sTEXT & varglcode & sDelimiter

'Generate the GL Account Text
strgltext = "GL Load Text_" & varglcode
sTEXT = sTEXT & strgltext & sDelimiter

'Generate GL Balance
varbalance = varbalance + 1
sTEXT = sTEXT & varbalance & sDelimiter

'Generate GL Type Code
'RandomNumber = Int((5 * Rnd) + 1)
Select Case Int((5 * Rnd) + 1)
Case 1
strgltypecode = "A"
Case 2
strgltypecode = "C"
Case 3
strgltypecode = "E"
Case 4
strgltypecode = "I"
Case 5
strgltypecode = "L"
End Select

sTEXT = sTEXT & strgltypecode & sDelimiter

'Generate Fund Code and Clase Load Code
Select Case Int((4 * Rnd) + 1)
Case 1
varfundcode = "FD01"
varclasscode = "FD01_Load_a"
Case 2
varfundcode = "FD01"
varclasscode = "FD01_Load_b"
Case 3
varfundcode = "FD02"
varclasscode = "FD02_Load_a"
Case 4
varfundcode = "FD02"
varclasscode = "FD02_Load_b"
End Select
sTEXT = sTEXT & varfundcode & sDelimiter & varclasscode & sDelimiter

'Generate Load Date
dtloaddate = "4/19/2009"
sTEXT = sTEXT & dtloaddate

Print #FileNum, sTEXT
sTEXT = ""

Close #FileNum

' If Err <> 0 Then
' ErrorMessageCount = ErrorMessageCount + 1
' ReDim Preserve ErrorMessage(1 To ErrorMessageCount)
' ErrorMessage(ErrorMessageCount) = "--There was an error saving the Bug Report to file."
' Err.Clear
' GoTo ResultMessages
' End If

End Sub
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Command/control + F. this will bring up a Search menu. Easy as pie.
after hitting control + F, hit options and select workbook to search the entire spreadsheet.
It is as easy as pie! Thanks jo!

This is a Rube Goldberg way to do this, but I've found it to be pretty effective.

Copy the entire spreadsheet and paste into a basic text editor like notepad.

Then copy the text from notepad into MS Word.

The first step is to replace all paragraph breaks with a non-numeric character but also one unlikely to appear in your data. I find that '|' is usually safe. Launch the search and replace box, enter ^p as the search string, and enter | as the replacement. Make sure you enable highlight formatting. Hit replace all. MS Word will convert all line breaks into the highlighted pipe character.

Next, you need to isolate the numbers. Open the search and replace tool. With the wildcards option enabled, enter the following as your search string: [0-9] . Set the replace option to be the 'find what text option' which is expressed as ^& . Enable formatting, and choose to make it highlighted. Then hit replace all. You will then see anything with a number now treated with a visual highlight.

Now it's time to get rid of the data you don't care about. From search and replace, you want to remove any character that is not highlighted. Choose '?' as your search string, choose 'not highlighted' from your format option, and enable wildcards. For replace, leave it blank. Hit replace all. You will then be left with only your numbers and pipes, all highlighted.

The last step is to reconvert the pipes into carriage returns. Search for '|' and replace with '^p'. You will then be left with your accounts, a carriage return reflecting each seperate record.

Copy the above, paste into notepad, then paste into a column in MS excel. Voila.

This method isn't great if your data includes numeric characters that you don't want. (numbers that aren't part of the account string)

You can write a VB function that does the above or use the macro recorder if you need to run this multiple times.

Again, definitely not elegant but works. I've found the wildcard tool in MS to be very robust, provided you are willing to get creative.
I want to make a logfile wherein i can keep notes recorded on various dates for use/review latter for followup action subsequently as and when required.