Can we use "*" to replace string in VBA excel macros ?

[Tomek]

Hello,

Can we use "*" to replace any text or spaces in macros ?

For example :

1)is string " quite new book xxxx " the same as string
" quite new" & "*" , probably not , I tried to do it like that but
it wasn't working at all.

2) how to write it in macro , if I want to compare a cell that is filled
only with a text and other cell if there is the same text but there are
also spaces and other text?


If there is the same text in both column, despite some additional text
or spaces in one cell, I want to offset(0,1) it.


Thank you very much for help

 

[Tom Ogilvy]

if Instr(1,LargeString, SmallerString,vbTextCompare) > 0 then

for example:
? instr(1,"AbCdEfGh","cde",vbTextCompare)
3

 

[Tomek]

1) If one string is in the begining of other string
2) if one string is in the middle of other strings
3) if one string is in the end of other string

if the string is found in the above situations I would like to have it
coppied and offset(0,1)

I can't make it !!!

Thank you very much for help

 

[Tom Ogilvy]

The example I gave would produce where the string is found. As long as it
isn't zero, you would copy. I am not sure why you can't make it. The
example needs no interpretation - When the condition is passed, you copy.

 

[Tomek]

The example I gave would produce where the string is found. As long as it
isn't zero, you would copy. I am not sure why you can't make it. The
example needs no interpretation - When the condition is passed, you copy.

I tried something like below but I do not know why it dosn't work

Sub proba2()
If InStr(1, "A1", "B1", vbTextCompare) > 0 Then
ActiveCell.Offset(10, 0).Activate
End If
End Sub


in cell A1 I had string "very big car"
in cell B1 I had string "big"

 

[Tom Ogilvy]

Sub proba2()
If InStr(1, Range("A1").Value, Range("B1").Value, vbTextCompare) > 0 Then
ActiveCell.Offset(10, 0).Activate
End If
End Sub

You were testing the string literals/constants A1 and B1, not what was in
the Cells A1 and B1.

 

[Tomek]

Sub proba2()
If InStr(1, Range("A1").Value, Range("B1").Value, vbTextCompare) > 0 Then
ActiveCell.Offset(10, 0).Activate
End If
End Sub

You were testing the string literals/constants A1 and B1, not what was in
the Cells A1 and B1.

yes yes yes !!!

You are wonderful !!!

Thank you very much indeed!!!

 

[Tomek]

Sub proba2()
If InStr(1, Range("A1").Value, Range("B1").Value, vbTextCompare) > 0 Then
ActiveCell.Offset(10, 0).Activate
End If
End Sub

You were testing the string literals/constants A1 and B1, not what was in
the Cells A1 and B1.

I wanted to change the "D" range but it doesn't work in the below
example. Is there another way to do it ?



Sub Comparison()
Dim Rng As Integer
Dim i As Integer
Rng = Selection.Rows.Count

For i = 2 To Rng
If InStr(1, Range("C2").Value, Range("D(i)").Value,
vbTextCompare) > 0 Then
ActiveCell.Offset(10, 0).Activate
End If
Next i
End Sub