Macro to Search in Colm A and replace in Colm E

PPuri

New Member
Joined
Mar 20, 2009
Messages
6
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Rather then Search and filter, does anyone know any macro that can perform the same.<o:p></o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
PPuri,

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


Excel Workbook
ABCDE
1FormsPrice
252644TOR$1.11
31234$0.00
412565$2.22
51235$0.00
61236$0.00
752644TOR$1.11
81234$0.00
912565$2.22
101235$0.00
111236$0.00
1252644TOR$1.11
131234$0.00
1412565$2.22
151235$0.00
161236$0.00
1752644TOR$1.11
Sheet1



After the macro:


Excel Workbook
ABCDE
1FormsPrice
252644TOR$19.60
31234$0.00
412565$19.60
51235$0.00
61236$0.00
752644TOR$19.60
81234$0.00
912565$19.60
101235$0.00
111236$0.00
1252644TOR$19.60
131234$0.00
1412565$19.60
151235$0.00
161236$0.00
1752644TOR$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).


Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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).

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top