Lookup / VBA help

[Mitas]

This should be a fairly easy fix, but I am racking my brain how to figure this one out and I hope one of you geniuses can help me out.

I have 4 columns of numbers

Numbers 1-30 are in column AH(15-44)

Numbers 31-60 are in column AL(15-44)

Numbers 61-90 are in column AQ(15-44)

Numbers 91-120 are in column AV(15-44)

I would like to look through these numbers and match the number that is entered in a merged cell (AI11-AM11). When it finds the match, I would like to put "END" beside the number that matches.

Example: if I enter 20 in AI11, I would like it to return "END" in AH34

Thanks in advance for your help. This will help me out so much

[mehmetcik]

Try this macro

Please Login or Register  to view this content.

[Mitas]

Thanks. This is helpful. Can you help me modify it a little farther?

When the value in AI11:AM11 changes, I would like the worksheet to automatically find the new "END" and remove the old one.

Example if the end changes from 20 to 30, I would like the end that was next to 20 removed and inserted next to the 30.

I appreciate all your help

[Richard Buttrey]

Hi

Why bother with a macro when there's a perfectly straighforward Excel function you can use.
e.g. in AI15 copied down

Please Login or Register  to view this content.

[Mitas]

Some times I'll have to enter text at other numbers besides the end. Entering text will remove the formula. This is why I was trying to go macro based so I wouldn't lose the formula whenever I put text in.

[mehmetcik]

Right click on the sheet name at the bottom of excel

select view code

Paste this code into the macro module

Close the macro module

change AH15

Please Login or Register  to view this content.

This version might be better:


Private Sub Worksheet_Change(ByVal Target As Range)



Set rnglook = Union(Range("AH15:AH44"), Range("AL15:AL44"), Range("AQ15:AQ44"), Range("AV15:AV44"))

If Not Intersect(Target, rnglook) Is Nothing Then
With rnglook

Set rngFind = .Find(Range("AI11").Value, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
rnglook.Offset(0, 1).Value = ""
rngFind.Offset(0, 1).Value = "End"
End If
End With

End If
End Sub

[Richard Buttrey]

Some times I'll have to enter text at other numbers besides the end. Entering text will remove the formula. This is why I was trying to go macro based so I wouldn't lose the formula whenever I put text in.

It always helps if you mention all conditions/restrictions in your first post so that we avoid wasting time. This is the first mention that you also want to be able to enter text. So please explain with some examples where you might want to enter text and what happens if that cell with text also meets the condition for satisfying your 'End' text requirement? Which takes precedence your text or the word End?

[Mitas]

Thanks for the help. It got me most of the way there and I was able to figure out the rest to get it where I wanted.

[mehmetcik]

Following Richards comments

I realise The modified code is useless for you.


Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)

Please Login or Register  to view this content.

[mehmetcik]

Duplicate Deleted.

[Mitas]

mehmetcik,

I have copied the code and ran it. when I do I get a compile error: invalid or unqualified reference. The error message highlights the second .cells. I have indicated where it is highlighting on the code below in red.

Set rnglook = Union(Range("AH15:AH44"), Range("AL15:AL44"), Range("AQ15:AQ44"), Range("AV15:AV44"))

If Not Intersect(Target, rnglook) Is Nothing Then
With rnglook

Set rngFind = .Find(Range("End").Value, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
rngFind.Offset(0, 1).Value = ""
End If
End With

Set rngFind = .Find(Range("AI11").Value, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
rngFind.Offset(0, 1).Value = "End"
End If
End With

End If
End Sub