Need to search cells in a row, find the first cell that contains a string

[jfoerch]

Hey all...
I'm trying to search for a specific string "CY" in a range of cells. Those cells will be within the range CT5 to EV5. I want to find the first cell in that row that has "CY" in it (only part of the contents of the cells) and then copy that cell and the next 19 in the row over to a different worksheet in the same workbook. I'm trying to a "with" and "find" routine that I've used elsewhere but I can't get it to work. Here is the code I'm trying to use. It doesn't even seem to start into that portion of the code so I wonder if I'm looking for a cell that has "CY" only in it.

dim c, a as variant
dim rng as range
With Worksheets("IHS_Data_Dump").Range("CT5:EV5")
    Set c = .Find("CY", LookIn:=xlValues)
    If Not c Is Nothing Then
        rng = c.Address
            For a = 0 To 18
                Worksheets("Master Program List").Cells(3, 25 + a) = rng.Offset(0, a)
            Next a

    End If
End With

Thanks.

[AB33]

You do not need to loop if the range to select is fixed,19. You can use the offset and resize functions. I do not know in which column you want to paste. You need to adjust the code as I have removed "a"

Option Explicit

Sub ntst()

Dim c As Range
Dim rng As String
With Worksheets("IHS_Data_Dump").Range("CT5:EV5")
    Set c = .Find("CY", LookIn:=xlValues)
    If Not c Is Nothing Then
        rng = c.Address
         Worksheets("Master Program List").Cells(3, 25) = c.Resize(19)
         c.Resize(19).Copy Worksheets("Master Program List").Cells(3, 25)
    End If
End With
End Sub

[jfoerch]

Sorry... I guess I wasn't clear enough. I want to copy the first cell that has "CY" in it and then also the next 18 cells (not characters within the cell). So, I want to find the first cell with "CY" in it and copy it to the destination and then do the same for the next 18 cells in the row after that first one. Is that more clear? Size of the cell doesn't matter.

[AB33]

I understand your request, but you have not provided me with a sample to test the code.
I will add next find function, but first you need to see if the code is working.
If the code finds the first match, it should copy 19 rows in to column A of the other sheet.

Option Explicit

Sub ntst()

Dim c As Range
Dim rng As String
With Worksheets("IHS_Data_Dump").Range("CT5:EV5")
    Set c = .Find("CY", LookIn:=xlValues)
    If Not c Is Nothing Then
        rng = c.Address
         c.Resize(19).Copy
         Worksheets("Master Program List").Cells(3, "A").PasteSpecial xlValues
    End If
End With
End Sub

[jfoerch]

Thanks for the reply. I tried it and it doesn't seem to find the first cell with "CY" in it that is in that range. Here is a file to help you help me figure out what isn't working. Thanks.

[AB33]

It does find "CY".
But all rows below CY are blanks
The question is- where do you want to paste it in sheet master once it found?
Is it row 3 column Y and where is the next row or column to be pasted?

[jfoerch]

Hmmmmm. OK. I'm trying to copy the cells to the right of the first cell with CY in it (including the first cell). Not underneath it. So, in this example, I want to be able to copy cells ED:5 through EV:5 and paste those values starting at Y:3 on the Master Program List tab and going to the right.

My problem is that I think the cell where I first see CY will change from time to time, so I can't choose a specific cell but I have to search for the first one.

Sorry if this is confusing.

[AB33]

If you want to search for next match, you need to decide where to paste it. I have assumed on the next empty column on row 3.
You should be able to adjust the remaining of the code, but the core of the code is working.

[jfoerch]

That is very strange. When I delete the row information that's in the destination location and run the code, nothing copies. I'm not sure why but it doesn't seem to be copying the information over.

[jfoerch]

For instance, I take the file you sent me and delete Y:3 through BQ:3. Then I go to "view Macros" and run the code. The file I'm attaching is what I get. I don't know what's happening because I can see that the code worked for you.

[AB33]

Step over the code using F8 and see for yourself when the code reaches the line copy and go back to sheet and see if the copy command is copying.

[jfoerch]

It skipped right from "If Not c Is Nothing Then" to "End If". It didn't even go into the loop section.

[AB33]

Well, in that case, you must have changed the layout of the sheets or you are working on different sample or sheet names. I have run the latest attached and got the same result.
I do not know what else do you want me to do.

[jfoerch]

OK. Thanks. I'll dig into it more later. I appreciate all the help. Sorry for the hassle.

[jfoerch]

Well, just restarted my computer and re-ran the code and now it works. Not sure what was going on. Thanks for all the help. And now I see what you were doing with the "resize" command. I thought you were re-sizing the cell with that but you were re-sizing the range. Sorry I missed that earlier. Thanks again!

[jfoerch]

AB33... If I can ask one more thing now that I have it working as you have shown. That first instance where "CY" was found... How can I store that location for use later? Later in the code, I want to use that location and copy/paste cell values as offsets from that original location. I've modified the sample file to show the code that I'm trying to use.

In the "Master Program List" tab I have several values in column A. Then in the "IHS_Data_Dump" tab, those same values exist in column B along with others. I want to take the values from the master program list tab, find where they exist on the data dump tab and then copy the corresponding data underneath the CY cells over to the master program list tab.

I confused myself just writing that, so hopefully you can follow. Any additional help would be greatly appreciated. Hopefully this is just an easy one.

[AB33]

Why are you looping again? Why can not you use the same copy method as the previous one?

[jfoerch]

That's a really good point. The resize that you showed me before is very slick. I guess I'm just used to doing the loop thing. But, I will use the resize where applicable from now on.

In this case I still have start the copy method that you showed me at the correct cell location... correct column (somewhere below the first "CY" column, ED in this case) and the correct row (corresponding to the value from column A on the Master Program List that I'm searching for on the data dump screen).

I was hoping that I could capture the first "CY" cell location when I found it the first time as a variable and then use offsets from that location later to start the copy of the row information underneath.

I'm sure there's an easy way to do this, but I'm still too much of a novice to figure it out.

[jfoerch]

OK. So I just solved part of my problem... I changed one of the lines to

c.Offset(1,0).Resize(0,18).copy

and it copied over the row of information directly below the original. So, I know how to do my offset copying now. Can I store that original CY location as a variable (say "CY") and then use the code

CY.Offset(1,0).Resize(0,18).copy

What would be the correct way to do that?

[AB33]

I have added comments and included some lines on how to use the offset.

[jfoerch]

Perfect. Was able to take what you gave me and fix my problems. Thanks!

[AB33]

You are welcome!
Please mark the thread as "Solved"

[jfoerch]

AB33,
Ran into another snag. Please see the attached file that shows more of my code and what I'm trying to do. Everything seems to run fine, but the cells that are copying based on offsets off of the "CY" cell won't copy down. I put a comment line at the bottom of the code that I'm trying to use so you can see what's not working right. Seems like the offsets aren't working, though they did work (with the .resize) command in the test file that I was working with before. Please take a look and see if you can figure out the problem for me.

Thanks.

[jfoerch]

Figured it out. I had to activate the worksheet that I wanted to copy the values from. I was just copying blank cells from a different worksheet.