Macro to Search in Colm A and replace in Colm E

[PPuri]

I need to comb through all tabs in a reports and ensure that the following forms a) 52644TOR b) 12565 (Listed in Column A) include the following pricing, $19.6, Listed in Column E.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> </o>
Rather then Search and filter, does anyone know any macro that can perform the same.<o></o>
<o> </o>
Thanks<o></o>

 

[stanleydgromjr]

PPuri,

Before the macro (identical Sheet1, Sheet2, and Sheet3):

Excel Workbook
	A	B	C	D	E
1	Forms				Price
2	52644TOR				$1.11
3	1234				$0.00
4	12565				$2.22
5	1235				$0.00
6	1236				$0.00
7	52644TOR				$1.11
8	1234				$0.00
9	12565				$2.22
10	1235				$0.00
11	1236				$0.00
12	52644TOR				$1.11
13	1234				$0.00
14	12565				$2.22
15	1235				$0.00
16	1236				$0.00
17	52644TOR				$1.11
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Forms				Price
2	52644TOR				$19.60
3	1234				$0.00
4	12565				$19.60
5	1235				$0.00
6	1236				$0.00
7	52644TOR				$19.60
8	1234				$0.00
9	12565				$19.60
10	1235				$0.00
11	1236				$0.00
12	52644TOR				$19.60
13	1234				$0.00
14	12565				$19.60
15	1235				$0.00
16	1236				$0.00
17	52644TOR				$19.60
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub UpdatePrice()
Dim wks As Worksheet
Dim c As Range, firstaddress As String
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets
  With wks.Columns(1)
    Set c = .Find("52644TOR", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        With c.Offset(, 4)
          .Value = 19.6
          .NumberFormat = "$#,##0.00"
        End With
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
  With wks.Columns(1)
    Set c = .Find("12565", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        With c.Offset(, 4)
          .Value = 19.6
          .NumberFormat = "$#,##0.00"
        End With
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
Next wks
Application.ScreenUpdating = True
End Sub

Then run the "UpdatePrice" macro.


Have a great day,
Stan

 

[PPuri]

Stan that's ecellent, however it seems to look up the form number in other worksheets and make changes there too!

Is there a way to have it run only in worksheet "Price File"?

Thanks again.

P

 

[PPuri]

Stan that's ecellent, however it seems to look up the form number in other worksheets and make changes there too!

Is there a way to have it run only in worksheet "Price File" (and/or both) "Sheet 1"

Thanks again.

P

 

[stanleydgromjr]

PPuri,

Your original request was for:

I need to comb through all tabs in a reports

Price File (and/or both) "Sheet1"

The new code below will run on worksheets "Price File", and "Sheet1".


Replace the old code with the following code.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Option Base 1
Sub UpdatePrice()
Dim i As Long
Dim c As Range, firstaddress As String
Dim wksArray As Variant
wksArray = Array("Price File", "Sheet1")
Application.ScreenUpdating = False
For i = LBound(wksArray) To UBound(wksArray)
  With Sheets(wksArray(i)).Columns(1)
    Set c = .Find("52644TOR", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        With c.Offset(, 4)
          .Value = 19.6
          .NumberFormat = "$#,##0.00"
        End With
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
  With Sheets(wksArray(i)).Columns(1)
    Set c = .Find("12565", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
      firstaddress = c.Address
      Do
        With c.Offset(, 4)
          .Value = 19.6
          .NumberFormat = "$#,##0.00"
        End With
        Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
  End With
Next i
Application.ScreenUpdating = True
End Sub

Then run the "UpdatePrice" macro.


Have a great day,
Stan

 

[PPuri]

Thank you so much my firiend. My mistake not making a correct request initially.

Highly appreciate the help; is there some book / sofware / teaching tool that is specifically desined to teach VBA? I write basic macros but want to learn more.

P

 

[stanleydgromjr]

PPuri,

MrExcel's books, and training material, are a good start.

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm


And, as you learn, put your new skills to work answering posts on MrExcel.


Have a great day,
Stan