Search offsetting if and copy and paste

[flynch]

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

 

[My Aswer Is This]

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"

 

[flynch]

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.

 

[My Aswer Is This]

Try this:

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

 

[My Aswer Is This]

If you need to do multiple locations use this:

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

 

[flynch]

Thank you.

Its says:

Run-time error '13':
Type mismatch

 

[My Aswer Is This]

Well I test all my scripts. Which script did you use. What do you have in E100

This is a very simple script

Thank you.

Its says:

Run-time error '13':
Type mismatch

 

[My Aswer Is This]

Do you have some other Vba scripting in your sheet?

 

[flynch]

No this is the only one

 

[flynch]

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