Excel Macro VBA Tip 4 - Find Cells in Excel with Macros using the Find Method

I a issue with the Excel FIND and the .FIND Method. I have some VBA that uses the .FIND method which looks for a date along a row of dates. See below. The code would not find the date. So i tried finding it using the Excel FIND and it would not find it also.

So in the options I change the lookin: from Values to formula and then it found it. I then changed the code to do the same and it now finds it. But what is puzzling is that the dates have be typed in as values and are not formulas, am I missing something.

sheet1

  A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG 1 Lookup Date   16/08/2010 26/07/2010           02/08/2010           09/08/2010           16/08/2010           23/08/2010           2 Venue Code Network Count 1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6 3 601 ESS 1 708           708   736       708   736       708           708           4 602 ESS 1 708           708   736       708   736       708           708          

Excel tables to the web >> Excel Jeanie HTML 4

Would I be correct in assuming that the Find and .Find method will not find dates example "02/08/2010" which resides in cells that have been formatted with "Centre Across Selection"

I had a bit a code with looked up dates that where in cells which used the formatting "Centre Across Selection" and noted it would not find them using the argument LookIn:=xlvalues but worked with LookIn:=xlFormulas.

So I tried the Excel find tool and noted the same. only when i removed the "Centre Across Selection2 formatting did it find the date using Values / xlvalues.

Is this correct?

My problem is with the line beginning "Selection.Find."

I want it to paste the clipboard contents into the search box, but it's
remembering the specific value used when I set up the macro (i.e. copied the
keystrokes).


Code:

    Selection.Copy
    Windows("Master TEST.xls").Activate
    Columns("D:D").Select
    Selection.Find(What:="359", After:=ActiveCell, LookIn:=xlFormulas,
    LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
        MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Range(ActiveCell, ActiveCell.Offset(0, 1)).Copy
    Windows("New.xls").Activate
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, -1).Select

Thanks for any help

I would like to do a simple search and replace of data in Excel and have created a macro to do this. The problem is that it errors if the data it is searching for doesn't exist in the area it's searching. I would like it to simply move on if it can't find a result. Here is my code

Option Explicit
Dim xrow As Integer
Dim curCellVal As String
Dim newsite
Dim wk1, wk2
Dim curRow
Sub change_locations()
Set wk1 = ThisWorkbook.Sheets("All_workstations_list-20080515-")
Set wk2 = ThisWorkbook.Sheets("Sites")
wk1.Activate
On Error GoTo skipRow
For xrow = 2 To wk1.UsedRange.Rows.Count
curCellVal = Trim(wk1.Cells(xrow, 2))
If curCellVal = "" Then GoTo skipRow
wk2.Activate

With wk2.Range("A:A")
.Find(what:=curCellVal, LookIn:=xlValues, searchorder:=xlByRows).Select
newsite = ActiveCell.Offset(0, 1).Value
End With

wk1.Cells(xrow, 8) = newsite

skipRow:
wk1.Activate
Next xrow

End Sub

I am trying to figure out how to make this work. What I want to do is:
I have a speadsheet with 7000 plus part numbers and I want to delete the colors from the part numbers using a macro. What I would really like is for the macro to look for values that are in a range and replace them in the parts list with nothing.

So my parts list looks like this
Sheet1
Column A
08445365800-082 08601223700- 08601223800- 08704236500L0BKP 08704236500L0RXP 08704236500R0RXP

Color Codes
Sheet2
Column A
082
0BKP
0RXP

So what I want the macro to do is remove what ever color codes are on Sheet2 column A from the Partslist on SHeet1 Column A. I thought about removing just the last 3 characters, but that doesn't work because I parts without colors. Any help would be greatly appreciated. I will need to be able to add more color codes to Sheet2 as I go.

Hello Sir,

Really, from last 1 month this problem is irritating me.

I have two worksheets.

Each sheet contains 30,000 rows of data in column A, C, E, G and I.
I want to find each cell of column A, C, E, G and I to another sheets column A, C, E, G and I respectively.
If match found then it should return "True" in column B, D, F, H and J.

I have tried VLookup, but it takes too much of time for calculation (1,50,000 vlookup Formulas needs to calculate in each sheet with If condition) some time calculation gives wrong results.

As well as I want to do filtering on this all the data... so that time calculation gives the problems (I have used application.Calculation=xlCalculationManual)

So now I am using Range.Find method, but that loop excecutes for 30,000 times and each ittration contains 5 Find methods.... This is again time consuming.

In case of more specification please let me know (Sorry for my poor English).

Please suggest the exact method for this....

Thanks you...

Hello,

I have a OLD OLD Excel 4 project. If you dont know let me give some info about it. The macros which are created in 4 is all listed at one excel sheet and they pass the reference through cell number. For convenience I have attached a snap shot of it. So you all know what I mean.

Now the question is: How can I export all the list of macros in any document (Excel, Word, etc.)?

Please let me know if anyone has some input in it. Thank You all.

-klm

Hi, I have a Excel Form on which I need to run a Macro to check some key cells and if the cells are empty, I want to inform the user which cell is empty and then have the macro go to the empty cell.

There may be multiple empty cells in the form

I have setup the following macro, but it doesn't seem to work. It only finds the first empty cell and then ignores any other empty cells.

Also not sure how to get the macro to return the user to each empty cell for required actions
I have added the macro I have put together below. Any assistance with this would be great.

VB:

Sub Check_For_Empty_Fields() 
     ' When user hits ADD RECORD Button, Macro checks for Missing Field Data for required upload file
    If Range("J58") = "" Then 
        MsgBox ("Please fill in Supplier Name in Section 3.") 
        Exit Sub 
    ElseIf Range("J60") = "" Then 
        MsgBox ("Please fill in Street Address 1 in Section 3.") 
        Exit Sub 
    ElseIf Range("J62") = "" Then 
        MsgBox ("Please fill in Street Address 2 in Section 3.") 
        Exit Sub 
    ElseIf Range("J64") = "" Then 
        MsgBox ("Please fill in Suburb in Section 3.") 
        Exit Sub 
    ElseIf Range("J66") = "" Then 
        MsgBox ("Please select State in Section 3.") 
        Exit Sub 
    ElseIf Range("P66") = "" Then 
        MsgBox ("Please fill in Postcode in Section 3.") 
        Exit Sub 
    ElseIf Range("Y64") = "" Then 
        MsgBox ("Please fill in Supplier Email Address in Section 3.") 
        Exit Sub 
    ElseIf Range("Y66") = "" Then 
        MsgBox ("Please fill in Remittance Method in Section 3.") 
        Exit Sub 
    ElseIf Range("J72") = "" Then 
        MsgBox ("Please select in AR Contact Telephone Area Code in Section 3.") 
        Exit Sub 
    ElseIf Range("N72") = "" Then 
        MsgBox ("Please fill in AR Contact Telephone Number in Section 3.") 
        Exit Sub 
    ElseIf Range("P72") = "" Then 
        MsgBox ("Please fill in AR Contact Telephone Number in Section 3.") 
        Exit Sub 
    ElseIf Range("J74") = "" Then 
        MsgBox ("Please select in AR Contact Fax Area Code in Section 3.If Fax Number Not Applicable select (NA) from list") 
        Exit Sub 
    ElseIf Range("N74") = "" Then 
        MsgBox ("Please fill in AR Contact Fax Number in Section 3.If Fax Number Not Applicable enter 0000 into field") 
        Exit Sub 
    ElseIf Range("P74") = "" Then 
        MsgBox ("Please fill in AR Contact Fax Number in Section 3.If Fax Number Not Applicable enter 0000 into field") 
        Exit Sub 
    ElseIf Range("D78") = "" Then 
        MsgBox ("Please fill in ABN number in Section 3.") 
        Exit Sub 
    ElseIf Range("J78") = "" Then 
        MsgBox ("Please fill in ABN number in Section 3.") 
        Exit Sub 
    ElseIf Range("F84") = "" Then 
        MsgBox ("Please fill in Bank BSB Number in Section 3.") 
        Exit Sub 
    ElseIf Range("F86") = "" Then 
        MsgBox ("Please fill in Bank Account Number in Section 3.") 
        Exit Sub 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Regards
Lance

I create a monthly report in which i create a couple of tables. For this the process of updating the information in tables has been automated. But one thing i need to do is update the name of the table each month. It would be like replacing a part of the name with the current month. (eg: Sales-Jan'08)
I have these names in a different sheet. so when i copy and paste this, the way the macro reads is the value and not the source of the value (eg:- i have Jan'08 in cell B59, instead of referencing the contents of B59, it takes Jan'08) so when i update the tables for the next month, the macro reads Jan'08 instead of Feb'08


I know this could be pretty simple, but i have just started with excel vba macros. Help would be appreciated.Thanks.