If statement in VBA

[kayla22222]

Hi,

I want to copy data in Column C if there is a "*" in column B on the same row. Then I want to paste it in the next available row in column C (or even starting at C700, then the next available). If there isn't a "*" then it should look at the next cell in the range.

I have two variations that are giving me problems:
First:

Sub CopyDataToSummary()
'
' CopyDataToSummary Macro
'
Dim cell As Range
For Each cell In Range("B116:B379")
If cell = "*" Then cell.Offset(0, 1).Select
Selection.Copy
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Next

End Sub

---------
This is giving problems, because once it gets to a cell without "*" it will copy the previous selection, since the "Selection.Copy" and everything below is not in the If statement.

I don't know how to do what I want in one line of VBA code for the if statement.

I tried to solve this by changing the Selection to select the cell in column B where the * will be....

Second:
Sub CopyDataToSummary()
'
' CopyDataToSummary Macro
'
Dim cell As Range
For Each cell In Range("B116:B379")
cell.Select
If cell = "*" Then cell.Offset(0, 1).Select
Selection.Copy
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Next

End Sub

-----------------------
This starts working, but once it hits a cell without a "*", it will copy the cell that has this formula in it: =IF(C353=" ","","*"). When it pastes this in the next available row in Column C, the offset command must see this as a 'non-blank' because it skips the row and the next * data will be down too many rows.

I think two if loops could work somehow, but I haven't been able to figure it out.

Sorry if this is confusing. Let me know if you need any clarification.
Thanks!!

 

[vaskov17]

Sub CopyDataToSummary()
    '
    ' CopyDataToSummary Macro
    '
    Dim cell As Range
        
    For Each cell In Range("B116:B379")
        If cell = "*" Then
            cell.Offset(0, 1).Copy
            Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next
End Sub

 

[kayla22222]

Wow, thanks! Thought it would be something easy like that.

If I want it to paste in a certain range, like starting at C700, and then the next available cell, how would I do that?

Thanks!!

 

[vaskov17]

Sub CopyDataToSummary()
    Range("B116:B379").AutoFilter 1, "~*"
    AutoFilter.Range.Offset(0, 1).Copy
    Range("C700").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
    AutoFilterMode = False
End Sub