Cut and paste value from fixed cell to a variable cell

[LalitPandey87]

As per your attached Screenshots following will work:

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "Drinks"
    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.Offset(, 1).Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub

[Charles_]

Thanks LalitPandey87!

I am getting Run - time error "Object variable or With block variable not set. It is highlighting the below line in bold.
I know I have to set the variables on that line but not sure where to.

Thanks again for your help!!


Sub Test2()

Dim strFormula As String
Dim rngRange As Range

Const strSearchCriteria As String = "Drinks"
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.Offset(, 1).Address(0, 0) & ","""")"
rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
End With

End Sub

As per your attached Screenshots following will work:

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "Drinks"
    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.Offset(, 1).Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub

[Rick Rothstein]

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)

Perfect... now I see what you are describing. Here is the code for the layout you showed in your pictures (but read on afterwards because I sense you actually need a more generic solution than you have asked for)...

Sub SwitchLetters()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate("IF(B2:B" & LastRow & "=""Drinks"",A1,C2:C" & LastRow & ")")
End Sub

The description for your original layout makes sense to me now. It also looks like you have a list of "items" that you are filling in prices for. So, let's go back to your originall described layout, but filled in the way I think it looks "at the beginning"... in other words, Column A (Row 2 on down) contain your items and Column B is blank. You then, I am guessing, want to put a word in Row 1 (I'll assume A1) and a price next to it (I'll assume in B1) and then I sense you would want to run code to fill in the number next to each item down the list in Column A that match the item specified in cell A1 using the number from B1. Then I imagine you want to change the value in A1 to a different item, put a different number in B1 and run the macro again having it fill the new number next to each item in Column A matching the item name in A1. I imagine you want to do this for your whole list. If I am right, give this macro a try (remember, your list is in A2 on down and A1 is where you put the item name to look up and B1 is where you put the number to fill next to that item)...

Sub FillInNumbers()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow) = Evaluate("IF(A2:A" & LastRow & "=A1,B1,IF(B2:B" & _
                                     LastRow & "="""","""",B2:B" & LastRow & "))")
End Sub

This is how I imagine things (see the tab names for explanations)...

[LalitPandey87]

But i didn't find any error. Working on my system.

[Charles_]

Hello Rick & LalitPandey87 ,

Thanks again for your persistance!

Rick, sorry for not providing the screenshots before as the code would have been much easier to troubleshoot and resolve, leasson learnt! Regarding the above, the first macros worked perfectly, the second one did not.

Basically, this is the very last step of the macros I am running and the total for "Drinks" I've calculated using a code to filter and sum all "beverages" and "alcohol" and copying in cell A1. I then had to delete all rows with "beverages" and "alcohol" , add a line at the end of the file with "Drinks" in Column B and pull the value from cell A1 into Column C.

I can't thank you enough for your dedication, I hope one day I can help somone in the same way.

Perfect... now I see what you are describing. Here is the code for the layout you showed in your pictures (but read on afterwards because I sense you actually need a more generic solution than you have asked for)...

Sub SwitchLetters()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate("IF(B2:B" & LastRow & "=""Drinks"",A1,C2:C" & LastRow & ")")
End Sub

The description for your original layout makes sense to me now. It also looks like you have a list of "items" that you are filling in prices for. So, let's go back to your originall described layout, but filled in the way I think it looks "at the beginning"... in other words, Column A (Row 2 on down) contain your items and Column B is blank. You then, I am guessing, want to put a word in Row 1 (I'll assume A1) and a price next to it (I'll assume in B1) and then I sense you would want to run code to fill in the number next to each item down the list in Column A that match the item specified in cell A1 using the number from B1. Then I imagine you want to change the value in A1 to a different item, put a different number in B1 and run the macro again having it fill the new number next to each item in Column A matching the item name in A1. I imagine you want to do this for your whole list. If I am right, give this macro a try (remember, your list is in A2 on down and A1 is where you put the item name to look up and B1 is where you put the number to fill next to that item)...

Sub FillInNumbers()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow) = Evaluate("IF(A2:A" & LastRow & "=A1,B1,IF(B2:B" & _
                                     LastRow & "="""","""",B2:B" & LastRow & "))")
End Sub

This is how I imagine things (see the tab names for explanations)...

[Rick Rothstein]

Rick, sorry for not providing the screenshots before as the code would have been much easier to troubleshoot and resolve, leasson learnt! Regarding the above, the first macros worked perfectly, the second one did not.

Basically, this is the very last step of the macros I am running and the total for "Drinks" I've calculated using a code to filter and sum all "beverages" and "alcohol" and copying in cell A1. I then had to delete all rows with "beverages" and "alcohol" , add a line at the end of the file with "Drinks" in Column B and pull the value from cell A1 into Column C.

I can't thank you enough for your dedication, I hope one day I can help somone in the same way.

First off, you are quite welcome... I was glad I was able to be of some help. As for the picture... as soon as I saw it, I knew exactly what you wanted (your written description was still useful... I used both of them to figure out what was needed), so I knew my first macro would work. As for the second macro... that was just a guess at what it looked like you were doing... I guessed wrong... it happens (way too much, unfortunately).