Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Cut and paste value from fixed cell to a variable cell

  1. #1
    Junior Member
    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0

    Cut and paste value from fixed cell to a variable cell

    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 & ")")"

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    This appears to be what you are asking for...
    Code:
    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".
    Last edited by Rick Rothstein; 01-04-2013 at 06:46 AM.

  3. #3
    Junior Member
    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    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!

    Quote Originally Posted by Rick Rothstein View Post
    This appears to be what you are asking for...
    Code:
    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".

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    May be this is what you are looking for

    Code:
    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
    Last edited by LalitPandey87; 01-04-2013 at 09:08 AM.

  5. #5
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    You can do it by using formula also

    Code:
    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
    Last edited by LalitPandey87; 01-04-2013 at 09:18 AM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Charles_ View Post
    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...
    Code:
    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.

  7. #7
    Junior Member
    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    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!


    Quote Originally Posted by LalitPandey87 View Post
    You can do it by using formula also

    Code:
    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

  8. #8
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Change below line of code

    Const strSearchCriteria As String = "A"

    with this one

    Const strSearchCriteria As String = "Drinks"
    Last edited by LalitPandey87; 01-04-2013 at 10:14 AM.

  9. #9
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Fully Agree with Rick

    Quote Originally Posted by Rick Rothstein View Post
    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.

  10. #10
    Junior Member
    Join Date
    Jan 2013
    Posts
    7
    Rep Power
    0
    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!!
    Attached Images Attached Images

Similar Threads

  1. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  2. Replies: 5
    Last Post: 03-09-2013, 09:01 AM
  3. Fixed asset Register
    By Howardc in forum Excel Help
    Replies: 1
    Last Post: 11-14-2012, 10:20 AM
  4. Replies: 1
    Last Post: 08-21-2012, 07:36 PM
  5. Trapping Copy To Range Before Copy/Cut Paste
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-07-2011, 07:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •