Quick Question: Wildcard 2

[apeoortje]

I have a quick question wouldn't take any of you a second to answer for me.

I'm trying to move cells in excel over by 1 if the value starts with Q3=

However I can't ge the wild card to work

This is what I have so far


Sub Test
Dim j As String
Range("B1").Select
j = Selection.Value
If j = "Q3=" + "?" Then
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End If
End Sub

 

[handleman]

You can't use wildcard characters with an equality comparator and strings.

Change your "If" statement to:

If "Q3=" = Left(j, 3) Then

Note that this comparison will be case-sensitive.

 

[johnwm]

You can simplify and tidy up a bit by using the Cut .. Destination syntax:

Public Sub moveif(myCell As Integer)
Dim j As String
    Cells(myCell, 1).Select
    j = Selection.Value
    If Left(j, 3) = "Q3=" Then
        Selection.Cut Destination:=Cells(myCell, 2)
    End If
End Sub

You can then call the sub with the row number as its argument


Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting
Steam Engine enthusiasts
Steam Engine Prints

 

[cummings54]

Select all the cells you want to test then run ReTest

Sub ReTest()
  Dim cl As Range
  For Each cl In Selection
    If cl.Value Like "Q3=*" Then
      cl.Offset(0, 1) = cl.Value
      cl.ClearContents
    End If
  Next cl
End Sub

 

[Harley78]

I am new (rookie=dumb)to VBA programing and wanted to know if someone can let me know what each line means....

Dim i, j, k, emptyrows, trow As Integer
Dim n1start As Integer
Dim n2start As Integer
Dim n3start As Integer
Dim txtstr1, txtstr2, txtstr3, txtstr4, txtstr5 As String
Dim tstr1, tstr2 As String
Dim HideTheRows As Boolean
Dim FoundItem As Boolean

Thanks

Bill

 

[handleman]

Here are your explanations:

Dim i, j, k, emptyrows, trow As Integer
-Declare i, j, k, emptyrows, and trow to be integer variables

Dim n1start As Integer
-Declare n1start to be an integer variable

Dim n2start As Integer
-Declare n2start to be an integer variable

Dim n3start As Integer
-Declare n3start to be an integer variable

Dim txtstr1, txtstr2, txtstr3, txtstr4, txtstr5 As String
-Declare txtstr1, txtstr2, txtstr3, txtstr4, and txtstr5 to be string variables

Dim tstr1, tstr2 As String
-Declare tstr1 and tstr2 to be string variables


Dim HideTheRows As Boolean
-Declare HideTheRows to be a Boolean variable

Dim FoundItem As Boolean
-Declare FoundItem to be a Boolean variable.


As an alternative to tacking on an irrelevant question post to a long-dead thread in a forum, you could try searching VBA help for certain key words, such as "Dim", "Integer", "String" or "Boolean".

 

[johnwm]

Actually

Dim i, j, k, emptyrows, trow As Integer

will dimension i, j, k and emptyrows as Variants. It will dimension trow as an Integer. Similarly

Dim txtstr1, txtstr2, txtstr3, txtstr4, txtstr5 As String

will dimension txtstr1, txtstr2, txtstr3, txtstr4 as Variants and txtstr5 to be a String.

It's a fairly common misunderstanding of the Dim statement which can lead to annoying bugs. If you do this in VB rather than VBA the problem is worse, as it causes a noticeable slowing of any looping code. It's also a good idea to use Longs instead of Integers, as Integers are internally converted to Longs, stored as Longs then converted back to Integers for any calculation or processing.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting
Steam Engine enthusiasts
Steam Engine Prints