Replacing several cells + creating a keyboard shortcut

**OM2** · 07-13-2011, 09:07 PM

I've got a very very small request - I was hoping someone could donate 1 min of their time!

i want this:

if cell in column a = "abc def" then replace column j in the same row with "=otherWorksheet!a4"

i need to do selective replacements - i will otherwise have to do the above manually - hundreds of times

i need instructions on how to write the above code + how to make it into a macro that i could execute with a keyboard shortcut (or buttonpress)

apologies if the above request is a cheeky one - i know i could do myself - but that might involve a few hours of putting my head down

thanks in advance if anyone can help

om

**Mordred** · 07-13-2011, 09:36 PM

Create a button and assign the following procedure

Sub OM2()
    Dim rCnt As Long, cl As Variant, ws As Worksheet
    Set ws = Sheets(1)
    rCnt = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For Each cl In ws.Range(Cells(1, 1), Cells(rCnt, 1))
        If cl.Value = "abc def" Then
            Cells(cl.Row, 10).Value = "=otherWorksheet!a4"
        End If
    Next cl
End Sub

It should work (hopefully).

**OM2** · 07-13-2011, 09:58 PM

mordred: thanks a million for the code
it does exactly what i asked for
but erm... i've only realised that my description wasn't good enough

- i should have mentioned that i need to do wildcard searches - so any cell in column D containing the text "abc def" should have cell J in the same row replaced with "=abc!d2"

- my experience of vba and excel is very very limited

i don't even know how to open up the vba part and enter the code u gave!
so, what i've done is created an empty macro and assigned a keyboard short cut
then i've inserted the code u gave

this worked ok with a dummy spreadsheet
but the spreadsheet i need to use on has 4 different worksheets and the code wouldn't quite work

let me know if u could suggest anything else

thanks

om

**Mordred** · 07-13-2011, 10:07 PM

Originally Posted by OM2

mordred: thanks a million for the code
it does exactly what i asked for
but erm... i've only realised that my description wasn't good enough

- i should have mentioned that i need to do wildcard searches - so any cell in column D containing the text "abc def" should have cell J in the same row replaced with "=abc!d2"

- my experience of vba and excel is very very limited

i don't even know how to open up the vba part and enter the code u gave!
so, what i've done is created an empty macro and assigned a keyboard short cut
then i've inserted the code u gave

this worked ok with a dummy spreadsheet
but the spreadsheet i need to use on has 4 different worksheets and the code wouldn't quite work

let me know if u could suggest anything else

thanks

om

Sorry I was reading too fast and only picked out certain words (in blue EDIT: [quotes don't show colours so look at the bold words

]) and thought you tried uploaded a workbook, that's why my last post is the way it is. If you have a mock workbook made though, please upload it because it will remove guess work on my behalf.

**Mordred** · 07-13-2011, 10:01 PM

Hi Omar, I would love to look at your workbook but you didn't upload it yet. Take your last post and click Edit and then Go Advanced and then select the paper clip button. follow the prompts after that.

Regards

**Mordred** · 07-13-2011, 10:09 PM

Also, do you want the rows in column A And column D to be searched or just column D?

**OM2** · 07-13-2011, 10:20 PM

Originally Posted by Mordred

Also, do you want the rows in column A And column D to be searched or just column D?

just need column d searched of the first sheet 'Sales'

i'll be off to bed in 5 min - it's nearly 3.30am where i am!

u've been a star - thank u

**OM2** · 07-13-2011, 10:14 PM

thanks for the reply
see attached to this post
i've stripped all sensitive data

so the code should go something like this:

- if string "my special text " is contained in a cell in column D
- then in column P of that same row, insert "=costPrice!d2"

i will then run this code repeatedly and change "my special text " and "=costPrice!d2"

i'm sure all of this sound like madness - but this will save me about 2 hours of work!

let me know if u can suggest anything - or if u could add code to the spreadsheet - that would be amazing

+ if u could give a few basic instructions of how i can add myself, that would awesome as well

THANK YOU!