Search offsetting if and copy and paste

flynch

New Member
Joined
Apr 3, 2017
Messages
17
Hello,

I have a huge table of place names and corresponding data. I now have an update for this data (these have been entered underneath the table) and need to to copy them in for the individual place names.

Therefore, for London for example, I would like to search the whole spreadsheet and look for london. When it finds london, I would like to move to the next cell along (one place to the right) and paste the data contained within cell E100.

I don't want it to look and change any of the data below the table (the recent updates) therefore I only want it to search down to row 92 and column BK.

I'm not too great at macros so if anyone can help me out with this - I'd be super grateful.

Thank you
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
So you want a script to search down Column "BK" starting in row (1) and continuing to row (92)

And if the script finds the word "London" then put the value found in range("E100) in Column "BL" next to "London"

Is that what you want?

Now you said "London" for example.

So if your going to do more places then "London"
Then will we always be looking in Column "BK"

And will we always be using the value in "E100"
 
Upvote 0
Hello sorry for not being clear.

The table runs to column Bk and down to row 92. It contains more than just London but I just wanted to do that one location for now.

The table is kind of in the format:

A B. C. D
1 London 6. Manchester. 11
2. York. 10. London. 23

Therefore, I would like it to search the entire table (from A1 to BK92 only) and every time it finds london, I want it to shift to the next cell on the right and change the existing number (copy and paste) to the figure found in E100.
 
Upvote 0
Try this:
Code:
Sub Test()
'Modified 12-1-17 7:35 AM EST
Application.ScreenUpdating = False
Dim c As Range
ans = Range("E100").Value
    For Each c In Range("A1:BK92")
    If c.Value = "London" Then c.Offset(0, 1).Value = ans
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
If you need to do multiple locations use this:

Code:
Sub Test()
'Modified 12-1-17 7:45 AM EST
Application.ScreenUpdating = False
Dim c As Range
Dim City As String
City = InputBox("Enter Location Name")

ans = Range("E100").Value
    For Each c In Range("A1:BK92")
    If c.Value = City Then c.Offset(0, 1).Value = ans
    Next
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
No this is the only one :(

It is highlighting only in yellow:

If c.value = "London" Then

Nothing else in script or that particular line is highlighted
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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