how can i move search to next cell if cell is empty? 1

[citizenzen]

Hi.

I am running a basic macro to search text within a long excel document. i am copying the contents of the search into a new excel document. the data is transposed. the problem is that some of the cells within the source document are blank.

ie:

User:
ID:
Email:
City:
Sector:

User:
ID:
Sector:

User:
ID:
Email:
Sector:

User:
ID:
Email:
City:
Sector:

so if the cell is empty, the contents of the next row are inserted into the blank cell! this is not what I need.

I need the data to look like the following:

User ID Email City Sector
User 1 78 xvy@yahoo.com Humble IT
User 13 45 Design
User 65 8 nyt@yahoo.com gary Network


and not:

User ID Email City Sector
User 1 78 xvy@yahoo.com Humble IT
User 13 45 nyt@yahoo.com gary User 65
User 65 8 nyt@yahoo.com gary Network

how can i accomplish this:

  For i = 0 To UBound(sInt, 2) Step 1
  
        lRow = 2
        
        For Each rCells In ws_source.Cells.Range("A1986:A2008")
        
        ws_dest.Range("A:G").EntireColumn.AutoFit
        
            If InStr(rCells, sInt(0, i)) Then
                '
                ws_dest.Cells(lRow, 1 + i) = Mid(rCells.Value, sInt(1, i))
                
                Set rCells = ws_source.Cells.Find(sInt(0, i))
                lRow = lRow + 1
                
            End If
        Next
    Next i

thanks in advance!

 

[SkipVought]

Hi,

How about this...

[b]
'you do not need to do this more than once[/b]
    ws_dest.Range("A:G").EntireColumn.AutoFit
    
    For i = 0 To UBound(sInt, 2) Step 1
    
        lRow = 2
        
        For Each rCells In ws_source.Cells.Range("A1986:A2008")
[b]        
            Select Case InStr(rCells, sInt(0, i))
                Case "User:", "ID:", "Email:", "City:", "Sector:"
                    ws_dest.Cells(lRow, 1 + i) = Mid(rCells.Value, sInt(1, i))
                    
                    Set rCells = ws_source.Cells.Find(sInt(0, i))
                    lRow = lRow + 1
            End Select[/b]
        Next
    Next i

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[citizenzen]

i get an empty sheet. no values are inserted.

 

[SkipVought]

I just did a copy 'n' paste. Looks like this statement needs to change, AT LEAST...

Select Case rCells.value

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[citizenzen]

i get a blank worksheet:

For i = 0 To UBound(sInt, 2) Step 1
    
        lRow = 2
        
        For Each rCells In ws_source.Cells.Range("A1986:A2000")
        ws_dest.Range("A:G").EntireColumn.AutoFit
        
            Select Case rCells.value                Case "User:", "ID:", "Email:", "City:", "Sector:"
                    ws_dest.Cells(lRow, 1 + i) = Mid(rCells.Value, sInt(1, i))
                    
                    Set rCells = ws_source.Cells.Find(sInt(0, i))
                    lRow = lRow + 1
            End Select
        Next
    Next i

i then tried:

For i = 0 To UBound(sInt, 2) Step 1
    
        lRow = 2
        
        For Each rCells In ws_source.Cells.Range("A1986:A2000")
        ws_dest.Range("A:G").EntireColumn.AutoFit
        
            Select Case InStr(rCells.Value, sInt(0, i))Case "User:", "ID:", "Email:", "City:", "Sector:"
                    ws_dest.Cells(lRow, 1 + i) = Mid(rCells.Value, sInt(1, i))
                    
                    Set rCells = ws_source.Cells.Find(sInt(0, i))
                    lRow = lRow + 1
            End Select
        Next
    Next i

and I still get a blank worksheet

 

[citizenzen]

as some of the strings don't exists, I tried the following, but i still get incorrect data in the transposed cells. is there an ifExists function?

For i = 0 To UBound(sInt, 2) Step 1
        lRow = 2
        For Each rCells In ws_source.Cells.Range("A1986:A2003")
        ws_dest.Range("A:G").EntireColumn.AutoFit
        'Set rCells = ws_source.Cells.Find(sInt(0, i))
        
            If InStr(rCells, sInt(0, i)) Then
            
            
            If rCells Is Nothing Then
             ws_dest.Cells(lRow, 1 + i) = "Empty"
             'lRow = lRow + 1
             
             Else
               
                ws_dest.Cells(lRow, 1 + i) = Mid(rCells.Value, sInt(1, i))
                lRow = lRow + 1
               
            End If
            End If
            
        Next
    Next i

 

[SkipVought]

Step thru your code and determine the values in rCells.Value. I assume that they are the values that you posted in your example. Maybe not. But we need to know.

Use the Watch Window as an aid.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[citizenzen]

thank you skipVought.

 

[SkipVought]

Problem solved?

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]