PDA

View Full Version : [SOLVED] Less specific search



Gil
12-16-2010, 09:51 AM
Hello
I was trying to modify this line


Range("K4").Value = Mid$(Range("B14").Value, 4, 3)

To something like this


ActiveCell.Value = Mid$(Range(.Offset(0,-9).Value, 4, 3)

The ActiveCell change seems ok but .Offset(0,-9) gives a syntax error.

slamet Harto
12-16-2010, 10:03 AM
like this:

ActiveCell.Value = Range("K4").Offset(0, -9)
ActiveCell.Value = Mid$(ActiveCell.Value, 4, 3)

Gil
12-16-2010, 11:02 AM
Hello
Thanks for your reply. The code you supplied refers to "K4".If you look at the spreadsheet you will see that I want to perform the action across several rows using a loop.

Gil
12-16-2010, 02:25 PM
I have now updated the macro on my workbook to run completely.If you look at the code you will see that it repeats itself many times. If anyone can suggest how I select 2 cells in a row other than


For Each cell In Range("I14:J14")

I think that I should be able to use a loop to do the job.
The answer may be obvious But not to me.

Bob Phillips
12-16-2010, 02:56 PM
Sub Macro10()
Dim lngLastRow As Long
Dim i As Long
lngLastRow = Range("j" & Rows.Count).End(xlUp).Row
Cells(4 - 0, 11).Resize(lngLastRow - 3).FormulaR1C1 = "=IF(RC[-9]="""","""",MID(RC[-9],FIND("""",RC[-9])+3,3))"
Cells(lngLastRow - 0, 11).Select
For i = lngLastRow To 4 Step -2
If InStr(Cells(i, "I").Value, Cells(i, "K").Value) > 0 Then
Cells(i, "J").Value = ""
ElseIf InStr(Cells(i, "J").Value, Cells(i, "K").Value) > 0 Then
Cells(i, "I").Value = Cells(i, "J").Value
Cells(i, "J").Value = ""
End If
Next i
Range("K1").Resize(lngLastRow).Clear
End Sub

Gil
12-16-2010, 07:57 PM
Hello xld
Well! I was not expecting a reply like that. Many thanks for your help in providing such a comprehensive solution. The example worksheet was not quite the finished layout so a couple of ammendments are required if possible.
The numbers in column B need to be offset 2 rows higher.
The data in columns I & J consist of 2 cells, one above the other so need to be moved together. New attachment with final layout
My appologies if this makes you scream.
Many thanks Gil

Bob Phillips
12-17-2010, 02:33 AM
Aaaaaaaaaaaaaaarghhhhhhhhhhhhh!

What is a UK bod doing up at 3:57AM?




Sub xld()
Dim lngLastRow As Long
Dim i As Long
lngLastRow = Range("j" & Rows.Count).End(xlUp).Row
Cells(5, 11).Resize(lngLastRow - 3).FormulaR1C1 = "=IF(R[-2]C[-9]="""","""",MID(R[-2]C[-9],FIND("""",R[-2]C[-9])+3,3))"
For i = 5 To lngLastRow Step 4
If InStr(Cells(i, "I").Value, Cells(i, "K").Value) > 0 Then
Cells(i - 1, "J").Value = ""
Cells(i, "J").Value = ""
ElseIf InStr(Cells(i, "J").Value, Cells(i, "K").Value) > 0 Then
Cells(i - 1, "I").Value = Cells(i - 1, "J").Value
Cells(i, "I").Value = Cells(i, "J").Value
Cells(i - 1, "J").Value = ""
Cells(i, "J").Value = ""
End If
Next i
Range("K1").Resize(lngLastRow).Clear
End Sub

Gil
12-17-2010, 06:56 AM
Hello xld
Fantastic. All works perfectly. Many thanks for your help without I would never get there.The code I was writing obviously is very basic and you supplied a super advanced solution which I really am gratefull for. However is there a laymans answer to my question regarding selecting the 2 cells For Each cell In Range("I14:J14").

Gil
A creature of the night (and day)

Bob Phillips
12-17-2010, 07:59 AM
Gil,

(hoping you are up at the moment :))

If I understand the question correctly, then




For Each cell In Range("I14:J14")


is a loop. I just removed it because it seems OTT for just two cells, especially as you might quit because the first meets the criteria.

Gil
12-17-2010, 08:10 AM
zld
Not sure if I understand but that can wait till another day.
Many thanks once again I will now mark it as solved.
Gil (25/8)