Thread: Search Range Macro Change Thanks: 0 Likes: 0

1. Search Range Macro Change

Hi

I have a macro, below, that works just fine but I was wondering if its possible to add one condition to it. If someone could just look to see if this is possible I would be very grateful.

- The macro searches for any number that is entered in cell B3.

- It searches the ranges C3:C20, F20: F40, I5:I 25, L 25:L40 (sample data ranges, many more) for the entered number. For a positive result the cell to the right of the entered number in cell B3 must have a #-# in it.

- The code then moves the set of numbers down one cell. As an example if there is a 1-1 in cell C3 the code would move it to cell C4 and the last number in the set would be increased by one to 1-2 (only the last number increases by one). The code is as follows:

Code:
If cell.Value = n And tmp Like "*#-#*" Then
Cells(irow + 1, icol).Insert Shift:=xlDown
Cells(irow, icol).Copy Cells(irow + 1, icol)
Cells(irow, icol).Clear
My change is if the set of numbers is 1-10 (could be any number  10, 4-10, 20-10 etc) the code would move the set of numbers down two cells instead of one cell. So as an example if cell C13 has a 1-10 the code would move it to cell C15 and the expected result would be 1-11.

If you have any questions please let me know.

Thank-you for any help.

Code:
Sub MOVE1DOWN()

Dim n, sht As Worksheet, cell As Range, num, tmp, rngDest As Range

Set sht = ActiveSheet

n = sht.Range("B3")

For Each cell In sht.Range("C3:C20, F20: F40, I5:I 25, L 25:L40").Cells

tmp = cell.Offset(0, 1).Value

If cell.Value = n And tmp Like "*#-#*" Then

'get the first number
num = CLng(Trim(Split(tmp, "-")(0)))
Debug.Print "Found a positive result in " & cell.Address

'find the next empty cell in the appropriate row
Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)
If rngDest.Column < 10 Then Set rngDest = sht.Cells(num, 10)

cell.Offset(0, 1).Copy rngDest

Rem Move the next row of cell contents to the next cell
In sht.Range(C3:C20, F20: F40, I5:I 25, L 25:L40).Cells

tmp = cell.Offset(0, 1).Value

If cell.Value = n And tmp Like "*#-#*" Then
Cells(irow + 1, icol).Insert Shift:=xlDown
Cells(irow, icol).Copy Cells(irow + 1, icol)
Cells(irow, icol).Clear

Exit For
End If
Next
End Sub 2. Re: Search Range Macro Change

I need to close this post it has the wrong macro and info. Sorry about that. User Tag List

Tags for this Thread

cell, cellsirow, number, search range for value, set  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•