If cell contain any of x text, then

[sdoppke]

Hi everyone can some one help me with a mod to my macro i need to have the macro start with checking sheet1 A1 for any text matching sheet2 a2. better desctription would be sheet1 A1 will contain july-11 and sheet 2 will contain july. If they match, then it will delpoy the "then" portion (A message box).


Thanks everyone in advance

sd

 

[Ruddles]

Something like this should do the job:-

with thisworkbook
  if instr(.sheets("sheet1").range("a1").value,.sheets("sheet2").range("a2").value)>0 then
    msgbox "sheet2!a2 found in sheet1!a1"
  endif
end with

 

[sdoppke]

Something like this should do the job:-

with thisworkbook
  if instr(.sheets("sheet1").range("a1").value,.sheets("sheet2").range("a2").value)>0 then
    msgbox "sheet2!a2 found in sheet1!a1"
  endif
end with

Thank you very much!! it works, is there a way to tweak it, so it that the text does not have to be exactly the same? one cell has July the other sais 7-1-11 (formated to say) July-11?

Thanks for your help

sd

 

[Ruddles]

Try:-

with thisworkbook
  if instr(.sheets("sheet1").range("a1").value,[COLOR=red][B]format([/B][/COLOR].sheets("sheet2").range("a2").value)[COLOR=red][B],"dd-mmmm-yyyy")[/B][/COLOR]>0 then
    msgbox "sheet2!a2 found in sheet1!a1"
  endif
end with

 

[sdoppke]

Try:-

with thisworkbook
  if instr(.sheets("sheet1").range("a1").value,[COLOR=red][B]format([/B][/COLOR].sheets("sheet2").range("a2").value)[COLOR=red][B],"dd-mmmm-yyyy")[/B][/COLOR]>0 then
    msgbox "sheet2!a2 found in sheet1!a1"
  endif
end with

Thank you again for the help. Looks like im getting a "type mismatch" error?

This is the exact code im using:

With ThisWorkbook
    If InStr(.Sheets("Dashboard").Range("O8").Value, Format(.Sheets("Quotas").Range("A2").Value), "mmmm-dd-yyyy") > 0 Then
    Response = MsgBox("this is the current month Quota Sheet, do you want to continue with replacement?", vbQuestion + _
vbYesNo)
If Response = vbYes Then

just changed the month to be first

sd

 

[Ruddles]

Does A2 contain an actual date serial? Format it as number and see if you get a number in the proximity of 40,700.

 

[sdoppke]

Does A2 contain an actual date serial? Format it as number and see if you get a number in the proximity of 40,700.

it comes in at 40725.00

sd

 

[Ruddles]

<deleted>

 

[Ruddles]

Okay, if Sheet2!A2 contains a date serial and you want to check whether the month part is found in the character string in Sheet1!A1, try this:-

With ThisWorkbook
    If InStr(.Sheets("Dashboard").Range("O8").Value, Format(.Sheets("Quotas").Range("A2").Value, "mmm")) > 0 Then
    Response = MsgBox("this is the current month Quota Sheet, do you want to continue with replacement?", vbQuestion + _
vbYesNo)
If Response = vbYes Then

If Sheet2!A2 contains a date serial and you want to check whether the month part is the same as the month part of the date serial in Sheet1!A1, try this:-

With ThisWorkbook
    If Format(.Sheets("Dashboard").Range("O8").Value, "mmm") = Format(.Sheets("Quotas").Range("A2").Value, "mmm") Then
    Response = MsgBox("this is the current month Quota Sheet, do you want to continue with replacement?", vbQuestion + _
vbYesNo)
If Response = vbYes Then

You have to ignore what the cells are displayed as - you can't compare these because they're not the actual values in the cell. You have to consider what the actual values in the cells are and compare thos in the most appropriate way.

 

[sdoppke]

Okay, if Sheet2!A2 contains a date serial and you want to check whether the month part is found in the character string in Sheet1!A1, try this:-

With ThisWorkbook
    If InStr(.Sheets("Dashboard").Range("O8").Value, Format(.Sheets("Quotas").Range("A2").Value, "mmm")) > 0 Then
    Response = MsgBox("this is the current month Quota Sheet, do you want to continue with replacement?", vbQuestion + _
vbYesNo)
If Response = vbYes Then

If Sheet2!A2 contains a date serial and you want to check whether the month part is the same as the month part of the date serial in Sheet1!A1, try this:-

With ThisWorkbook
    If Format(.Sheets("Dashboard").Range("O8").Value, "mmm") = Format(.Sheets("Quotas").Range("A2").Value, "mmm") Then
    Response = MsgBox("this is the current month Quota Sheet, do you want to continue with replacement?", vbQuestion + _
vbYesNo)
If Response = vbYes Then

You have to ignore what the cells are displayed as - you can't compare these because they're not the actual values in the cell. You have to consider what the actual values in the cells are and compare thos in the most appropriate way.

the first one did the trick!! Phenominal thanks for sticking with me.

and thanks for the explanation.

sd