Cut and paste value from fixed cell to a variable cell

[Charles_]

Hello,

I am trying to run:

*Cut value from cell A1
*Paste value in column B, only IF column A equals "Drinks"

Rick provided the code below for a similar requirement but would like to edited for th above.

Thanks in advance for your help!

"Dim Amount As Long
Amount = Cells(Rows.Count, "B").End(xlUp).Row
Range("F2:F" & Amount) = Evaluate("IF(B2:B" & Amount & "=""OK"",IN2:IN" & Amount & ",IO2:IO" & Amount & ")")"

[Rick Rothstein]

This appears to be what you are asking for...

Sub DrinksAnyone()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate("IF(A1:A" & LastRow & "=""Drinks"",""Drinks"",B1:B" & LastRow & ")")
  Columns("A").ClearContents
End Sub

Note: That last line is there because you said you wanted to "*Cut value from cell A1".

[Charles_]

Hi Rick,

Thanks for your help.

The code is not working though, I may have explained incorrectly.

*I will always have a variable value in cell A1
*I need to be able to paste that value in column C only IF column B = Drinks.

The problem I am having is that is currently copying "0" and not "100" in this case. I believe its because I had the value in the same column as the delimiter (Drinks). I have moved the delimiter to column B.

Thanks again!

This appears to be what you are asking for...

Sub DrinksAnyone()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate("IF(A1:A" & LastRow & "=""Drinks"",""Drinks"",B1:B" & LastRow & ")")
  Columns("A").ClearContents
End Sub

Note: That last line is there because you said you wanted to "*Cut value from cell A1".

[LalitPandey87]

May be this is what you are looking for

Sub Test()

    Dim rngRange                            As Range
    Dim rngCell                             As Range
    Dim lngCount                            As Long
    
    Const strSearchCriteria                 As String = "A"
    Const strDataStartCell                  As String = "A1"
    Const intColNoToFilter                  As Integer = 2
    Const intOutputColumnNo                 As Integer = 3
    
    ReDim varData(0)
    With ThisWorkbook.Worksheets("sheet1")
    
        .AutoFilterMode = False
        Set rngRange = .Range(strDataStartCell).CurrentRegion
        
        With rngRange
            .AutoFilter
            .AutoFilter Field:=intColNoToFilter, Criteria1:=strSearchCriteria
        End With
        
        On Error Resume Next
        Set rngRange = rngRange.Resize(, 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0: Err.Clear
        
        lngCount = 0
        For Each rngCell In rngRange
            If lngCount = 1 Then
                rngCell.Offset(, intOutputColumnNo - 1).Value = rngCell.Value
            Else
                lngCount = 1
            End If
        Next rngCell
        
        .AutoFilterMode = False
        
    End With

End Sub

[LalitPandey87]

You can do it by using formula also

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "A"
    Const strDataStartCell                  As String = "A1"
    Const intColNoToFilter                  As Integer = 2
    Const intOutputColumnNo                 As Integer = 3
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)
        Set rngRange = Intersect(rngRange, rngRange.Offset(1))
        strFormula = "=IF(" & rngRange.Offset(, 1).Address(0, 0) & "=""" & strSearchCriteria & """," & rngRange.Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub

[Rick Rothstein]

The code is not working though, I may have explained incorrectly.

*I will always have a variable value in cell A1
*I need to be able to paste that value in column C only IF column B = Drinks.

The problem I am having is that is currently copying "0" and not "100" in this case. I believe its because I had the value in the same column as the delimiter (Drinks). I have moved the delimiter to column B.

Now you have changed from cutting values to simply copying them, so I don't know whether the values in Column A (or B for that matter) are supposed to stay or be removed. I have chosen not to remove them. See if this code does what you want...

Sub DrinksAnyone()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate("IF(B1:B" & LastRow & "=""Drinks"",""Drinks"",IF(C1:C" & _
                                      LastRow & "="""","""",C1:C" & LastRow & "))")
End Sub

Note for future questions or possible follow-ups to this one... a picture is really worth a thousand words. Simply show us a relatively accurate sample of your existing setup (may 10 or so rows) and for that same data show us what you want it to look like afterwards. You can do that by a typed-in set of spaced data or, better yet, attach a sample workbook in which you clearly identify for us what is the existing and what is the final after-processing result. You can still try to explain in words what you want (the extra details you provide may supply valuable additional information), but the "picture" would be so much more useful.

[Charles_]

Thank you LalitPandey87,

Could you please mention where should I be inserting the "Drinks" variable in the code below? I dont see where am I telling VBA to copy cell A1 to column F only if B is Drinks.

Sorry, I am quite new to VBA

Thanks!

You can do it by using formula also

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "A"
    Const strDataStartCell                  As String = "A1"
    Const intColNoToFilter                  As Integer = 2
    Const intOutputColumnNo                 As Integer = 3
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)
        Set rngRange = Intersect(rngRange, rngRange.Offset(1))
        strFormula = "=IF(" & rngRange.Offset(, 1).Address(0, 0) & "=""" & strSearchCriteria & """," & rngRange.Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub

[LalitPandey87]

Change below line of code

Const strSearchCriteria As String = "A"

with this one

Const strSearchCriteria As String = "Drinks"

[LalitPandey87]

Fully Agree with Rick

Note for future questions or possible follow-ups to this one... a picture is really worth a thousand words. Simply show us a relatively accurate sample of your existing setup (may 10 or so rows) and for that same data show us what you want it to look like afterwards. You can do that by a typed-in set of spaced data or, better yet, attach a sample workbook in which you clearly identify for us what is the existing and what is the final after-processing result. You can still try to explain in words what you want (the extra details you provide may supply valuable additional information), but the "picture" would be so much more useful.

[Charles_]

Hi Rick,

Thanks again for your help.

Following your advise, please find below an example of the worksheet attached.

The code is pasting the word "Drinks" in column C rather than the value in cell A1 (100)

Thanks again!!