0

I am trying to copy Cells from one Worksheet to another Worksheet if a giving condition is met. I know how to make it work with one value using the following code :

Private Sub CommandButton1_Click()
  Dim CustomerName As String, Phone As String
  Worksheets("Tabelle1").Select
  CustomerName = Range("A2")
  Phone = Range("B2")
  Worksheets("Tabelle2").Select
  Worksheets("Tabelle2").Range("A1").Select
  If Worksheets("Tabelle2").Range("A1").Offset(1, 0) <> "" Then
    Worksheets("Tabelle2").Range("A1").End(xlDown).Select
  End If

  If Worksheets("Tabelle1").Range("B2").Value = "Pooe" Then
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = CustomerName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Phone
  End If
End Sub

If I now try to the same with the entire column I get the "type mismatch" error.

Private Sub CommandButton1_Click()
  Dim CustomerName As String, Phone As String
  Worksheets("Tabelle1").Select
  CustomerName = Range("A2")
  Phone = Range("B2")
  Worksheets("Tabelle2").Select
  Worksheets("Tabelle2").Range("A1").Select
  If Worksheets("Tabelle2").Range("A1").Offset(1, 0) <> "" Then
    Worksheets("Tabelle2").Range("A1").End(xlDown).Select
  End If

  If Worksheets("Tabelle1").Range("B:B").Value = "Pooe" Then
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = CustomerName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Phone
  End If
End Sub

The goal is to copy the entire row of the first worksheet to the second worksheet, if in the first worksheet the condition (Column B = "Pooe" ) is matched. Does anybody know how to make it work ?

Dominique
  • 16,450
  • 15
  • 56
  • 112
WhosWho
  • 1
  • 4
  • What is the top-most cell in column `B` of `Tabelle1` where you want to look for `Pooe`? You don't want to look in `B2` and you don't want to look in the header. Why don't you share the screenshots of your worksheets? – VBasic2008 Aug 22 '22 at 13:25
  • It starts at B2. B2 is the topmost cell in Column B. B1 would be the header. – WhosWho Aug 22 '22 at 13:27

1 Answers1

0
Worksheets("Tabelle1").Range("B2").Value = "Pooe"

=> this makes sense: you can have a string in just one single cell, like "B2".

Worksheets("Tabelle1").Range("B:B").Value = "Pooe"

=> this makes no sense: one string can not be the value of an entire column. Ok, I admit, if you merge the whole column it might work, but that would have absolutely no sense.

Why do you even want to replace "B2" by "B:B" if the first one is working fine?

One thing I should say: you are using far too many Select commands in your code, please check this URL on how to avoid them.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • I want to replace it because I need to know if B3, B4, B5,and so on contain that string. This allows me only to compare B2 but I need to compare the entire Column B. – WhosWho Aug 22 '22 at 13:25
  • In that case, I would advise you to work with a `For` loop. I have created this very simple `For`-loop, checking which cell in "B2:B6" does not contain "Poe": `For Each c In Range("B2", "B6") If c.Value <> "Poe" Then Range("E5").Value = c.Row End If Next`. – Dominique Aug 22 '22 at 13:34