Search + If Statement

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
414
Ive searched and searched to try and find what I am looking for but I can't find it.:eek:

This is what I am trying to do.

Search Column AV for "CALCA"
Then look at Column AL in that row for data.

If there is data in that cell then do nothing.

If there is no data in that cell then do the following:

[IM GOING TO USE ROW 1 FOR EXAMPLE]

Cell AL1=K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&Q1&" "&R1
Cell AM1=S1
Cell AN=T1
Cell AO1=U1

Hope that is not too complicated.

My goal with this macro is the following, CALCA is a type of customer. Look to see if they have mailing address if they do then do nothing. If they dont then put the residence address as their mailing address. ;)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
If I am understanding you correctly, this should work...
=if(and(AV1="CALCA",AL1=""),K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&Q1&" "&R1,AL1)
That would put the formula indicated in each cell where AL is empty and the address in AL1 if it is already there.

Does that help?
 
Upvote 0
That formula does work, but the records I will be searching through has several thousand records so I am looking for a macro to automate it for me.
 
Upvote 0
If the 1000 rows are in succession with no blank entries to the right, then write the formula once, go to the lower right corner intil you get the fine crosshairs and double click. That should coipy your formula to the bottom.

If you are looking for macro help, what do you want the macro to do?
 
Upvote 0
If the 1000 rows are in succession with no blank entries to the right, then write the formula once, go to the lower right corner intil you get the fine crosshairs and double click. That should coipy your formula to the bottom.

If you are looking for macro help, what do you want the macro to do?

The only problem I see with that is that if there is information in cell then it will be overwritten by the formula.

I have described the macro help in my first post. :)
 
Upvote 0
I saw the macro request afterwards, sorry.

Well, you could run a loopping macro, but those can take a while.
I think I have another simple solution for you.

Write this formula once in ANY cell - it doesn't have to be in your column we are working on
=if(AV1="CALCA",K1&" "&L1&" "&M1&" "&N1&" "&O1&" "&Q1&" "&R1,"")
Copy it
Select your range for column AL
If it is really long with breaks, then click on another continuous column and hit Ctrl + Down arrow. Take notice of the bottom row number. Let's assume it is row 1234

ctrl home to get to top
go to your name box, left of your formula bar and type in AL2:AL1234
(this assumes AL2 is the first cell with addresses)

Now click Ctrl + G
or
Edit - Goto

Click Special in the bottom left

Click Blanks

Click OK

Now click in the formula bar and click CTRL + V to paste

Finally, press CTRL + Enter and the same formula will be written in every blank cell, but only the blanks.

Yes you could do a Macro, but I think you will find this faster.
Does that solve your problem?
 
Upvote 0
Here's a macro that seems to do the trick. Test in a COPY of your workbook. No columns are hard-coded, they're all variables. This way if you change where the data is, you can easily change the macro too.

Code:
Sub test_without_column_P()

Dim cell As Range
Dim firstlook As Range
Dim FSC As Integer  'FSC=First Search Column, column number in which to look for search string
Dim SSC As Integer  'SSC=Second Search Column, column number in which to look for criteria
Dim RC(4) As Integer  'RC(1)=Results Column 1, first column to paste a value to

Dim lastrow As Long, firstrow As Long
Dim look4 As String
Dim move(4) As String
Dim D(10) As Integer  'column numbers to combine for data copying

FSC = 48 'Column AV=48
SSC = 38 'Column AL=38

RC(1) = 38  'Column AL=38
RC(2) = 39  'Column AM=39
RC(3) = 40  'Column AN=40
RC(4) = 41  'Column AO=41

D(1) = 11  'Column K
D(2) = 12  'Column L
D(3) = 13  'Column M
D(4) = 14  'Column N
D(5) = 15  'Column O
D(6) = 17  'Column Q
D(7) = 18  'Column R

D(8) = 19  'Column S
D(9) = 20  'Column T
D(10) = 21  'Column U

look4 = "CALCA"
firstrow = 2 'assuming a header row in row 1
lastrow = Cells(Rows.Count, FSC).End(xlUp).Row  'finds the last row of data in the first search column

Set firstlook = Range(Cells(firstrow, FSC), Cells(lastrow, FSC))

For Each cell In firstlook
    If cell.Value = look4 Then
        If cell.Offset(0, SSC - FSC).Value = "" Then
            For i = 1 To 7
            move(1) = move(1) & Cells(cell.Row, D(i)).Value
            Next i
            For i = 2 To 4
            move(i) = Cells(cell.Row, D(i + 6)).Value
            Next i
            
            For i = 1 To 4
            Cells(cell.Row, RC(i)).Value = move(i)
            move(i) = ""
            Next i
        End If
    End If
Next cell

End Sub



I noticed that you skipped column P when compiling the address. In case that was a typo, here's code that includes column P as well:

Code:
Sub test_with_column_P()

Dim cell As Range
Dim firstlook As Range
Dim FSC As Integer  'FSC=First Search Column, column number in which to look for search string
Dim SSC As Integer  'SSC=Second Search Column, column number in which to look for criteria
Dim RC(4) As Integer  'RC(1)=Results Column 1, first column to paste a value to

Dim lastrow As Long, firstrow As Long
Dim look4 As String
Dim move(4) As String
Dim D(11) As Integer  'column numbers to combine for data copying

FSC = 48 'Column AV=48
SSC = 38 'Column AL=38

RC(1) = 38  'Column AL=38
RC(2) = 39  'Column AM=39
RC(3) = 40  'Column AN=40
RC(4) = 41  'Column AO=41

D(1) = 11  'Column K
D(2) = 12  'Column L
D(3) = 13  'Column M
D(4) = 14  'Column N
D(5) = 15  'Column O
D(6) = 16  'Column P
D(7) = 17  'Column Q
D(8) = 18  'Column R

D(9) = 19  'Column S
D(10) = 20  'Column T
D(11) = 21  'Column U

look4 = "CALCA"
firstrow = 2 'assuming a header row in row 1
lastrow = Cells(Rows.Count, FSC).End(xlUp).Row  'finds the last row of data in the first search column

Set firstlook = Range(Cells(firstrow, FSC), Cells(lastrow, FSC))

For Each cell In firstlook
    If cell.Value = look4 Then
        If cell.Offset(0, SSC - FSC).Value = "" Then
            For i = 1 To 8
            move(1) = move(1) & Cells(cell.Row, D(i)).Value
            Next i
            For i = 2 To 4
            move(i) = Cells(cell.Row, D(i + 7)).Value
            Next i
            
            For i = 1 To 4
            Cells(cell.Row, RC(i)).Value = move(i)
            move(i) = ""
            Next i
        End If
    End If
Next cell

End Sub


If you need spaces between the items that make up the first address data, replace this line of code
Code:
move(1) = move(1) & Cells(cell.Row, D(i)).Value


with this:
Code:
move(1) = move(1) & Cells(cell.Row, D(i)).Value & " "
 
Last edited:
Upvote 0
Here's a macro that seems to do the trick. Test in a COPY of your workbook. No columns are hard-coded, they're all variables. This way if you change where the data is, you can easily change the macro too.

Code:
Sub test_without_column_P()

Dim cell As Range
Dim firstlook As Range
Dim FSC As Integer  'FSC=First Search Column, column number in which to look for search string
Dim SSC As Integer  'SSC=Second Search Column, column number in which to look for criteria
Dim RC(4) As Integer  'RC(1)=Results Column 1, first column to paste a value to

Dim lastrow As Long, firstrow As Long
Dim look4 As String
Dim move(4) As String
Dim D(10) As Integer  'column numbers to combine for data copying

FSC = 48 'Column AV=48
SSC = 38 'Column AL=38

RC(1) = 38  'Column AL=38
RC(2) = 39  'Column AM=39
RC(3) = 40  'Column AN=40
RC(4) = 41  'Column AO=41

D(1) = 11  'Column K
D(2) = 12  'Column L
D(3) = 13  'Column M
D(4) = 14  'Column N
D(5) = 15  'Column O
D(6) = 17  'Column Q
D(7) = 18  'Column R

D(8) = 19  'Column S
D(9) = 20  'Column T
D(10) = 21  'Column U

look4 = "CALCA"
firstrow = 2 'assuming a header row in row 1
lastrow = Cells(Rows.Count, FSC).End(xlUp).Row  'finds the last row of data in the first search column

Set firstlook = Range(Cells(firstrow, FSC), Cells(lastrow, FSC))

For Each cell In firstlook
    If cell.Value = look4 Then
        If cell.Offset(0, SSC - FSC).Value = "" Then
            For i = 1 To 7
            move(1) = move(1) & Cells(cell.Row, D(i)).Value
            Next i
            For i = 2 To 4
            move(i) = Cells(cell.Row, D(i + 6)).Value
            Next i
            
            For i = 1 To 4
            Cells(cell.Row, RC(i)).Value = move(i)
            move(i) = ""
            Next i
        End If
    End If
Next cell

End Sub

I noticed that you skipped column P when compiling the address. In case that was a typo, here's code that includes column P as well:

Code:
Sub test_with_column_P()

Dim cell As Range
Dim firstlook As Range
Dim FSC As Integer  'FSC=First Search Column, column number in which to look for search string
Dim SSC As Integer  'SSC=Second Search Column, column number in which to look for criteria
Dim RC(4) As Integer  'RC(1)=Results Column 1, first column to paste a value to

Dim lastrow As Long, firstrow As Long
Dim look4 As String
Dim move(4) As String
Dim D(11) As Integer  'column numbers to combine for data copying

FSC = 48 'Column AV=48
SSC = 38 'Column AL=38

RC(1) = 38  'Column AL=38
RC(2) = 39  'Column AM=39
RC(3) = 40  'Column AN=40
RC(4) = 41  'Column AO=41

D(1) = 11  'Column K
D(2) = 12  'Column L
D(3) = 13  'Column M
D(4) = 14  'Column N
D(5) = 15  'Column O
D(6) = 16  'Column P
D(7) = 17  'Column Q
D(8) = 18  'Column R

D(9) = 19  'Column S
D(10) = 20  'Column T
D(11) = 21  'Column U

look4 = "CALCA"
firstrow = 2 'assuming a header row in row 1
lastrow = Cells(Rows.Count, FSC).End(xlUp).Row  'finds the last row of data in the first search column

Set firstlook = Range(Cells(firstrow, FSC), Cells(lastrow, FSC))

For Each cell In firstlook
    If cell.Value = look4 Then
        If cell.Offset(0, SSC - FSC).Value = "" Then
            For i = 1 To 8
            move(1) = move(1) & Cells(cell.Row, D(i)).Value
            Next i
            For i = 2 To 4
            move(i) = Cells(cell.Row, D(i + 7)).Value
            Next i
            
            For i = 1 To 4
            Cells(cell.Row, RC(i)).Value = move(i)
            move(i) = ""
            Next i
        End If
    End If
Next cell

End Sub
If you need spaces between the items that make up the first address data, replace this line of code
Code:
move(1) = move(1) & Cells(cell.Row, D(i)).Value
with this:
Code:
move(1) = move(1) & Cells(cell.Row, D(i)).Value & " "

This is ALMOST perfect. I just need a space between the data.

Right now when column AL copies from columns K:R the result shows 701NMAINST when it should be 701 N MAIN ST.

Very exciting! :biggrin:
 
Upvote 0
In whichever version of the macro you ended up using, replace this line of code:

Code:
move(1) = move(1) & Cells(cell.Row, D(i)).Value


with this line of code:

Code:
move(1) = move(1) & Cells(cell.Row, D(i)).Value & " "


That line appears only once in the macro, and this new line adds a space between each element.
 
Upvote 0
LOL....yes that did it. Thanks.

I feel foolish because I remember reading that part in your original post...and then forgot about it later on.

Thank you though!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top