1

Excel Background
I got 2 excel column and long records of rows. (example only, as I have more than 10 column)

Column A - It is Either "Yes or No" Column B - Is a remark. Can type anything I need


How it works
This is an ordering system, if date is delayed, Column A will show "Yes" If no Delay, it will show "No"


I need
I need the VBA code to perform the following:

If column B2 contains text "No Delay", Column A2 will change from "Yes" to "No"


What I have tested

    Private Sub UpdateColumnA()
    Dim x As Long
    For x = 4 To 65536
    Dim InStr As String

    If InStr(1, Sheet1.Range("$M$" & x), "No Delay") Then
        Sheet1.Range("$K$" & x) = Sheet1.Range("$K$" & x) & "No"
        End If

    Next
    End Sub

Problem I met above is that, Column A2 "Yes" will become "YesNo". I require to replace the Yes to No instead of adding to the existing text.

And if B2 text "No Delay" does not match exactly, it will not work at all, hence I want it to be non case sensitive, such as "nO Delay" "No DeLAy" will also work.

user1935007
  • 51
  • 2
  • 7

1 Answers1

2

The option vbTextCompare could make the comparison non case sensitive:

InStr(1, Sheet1.Range("$M$" & x), "No Delay", vbTextCompare)

And a simple assignation would avoid the concatenation of values ("YesNo") in the cell:

Sheet1.Range("$K$" & x).Value = "No"
VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
  • Now instead changing to "No". It change to "false". – user1935007 Dec 29 '12 at 08:45
  • @user try = "" & "No", in order to force a *string* value. – VonC Dec 29 '12 at 08:55
  • is there any other method or other vba code able to make this work? Thanks for the continuous help too :) – user1935007 Dec 29 '12 at 17:28
  • @user1935007 Try `Sheet1.Range("$K$" & x).Value = "No"`, or `Sheet1.Range("$K$" & x).Text = "No"` – VonC Dec 29 '12 at 17:36
  • Sheet1.Range("$M$" & x) = Sheet1.Range("$M$" & x).Value = "N" or Sheet1.Range("$M$" & x) = Sheet1.Range("$M$" & x).text = "N" doesn't work at all. Still getting the word "False" or "True" instead of Y or N – user1935007 Dec 30 '12 at 05:52
  • @user no, I meant only one =: xxx.Value = "N", not xxx = xxx.Value = "N" – VonC Dec 30 '12 at 07:56
  • @user But if it is *still* not working, check the *format* of the cell you want to write into: That format might be set to boolean, and trigger the display False when the value is No (or anything besides 0, for that matter) – VonC Dec 30 '12 at 08:02
  • thanks. one =: xxx.Value = "N" , solved my issue. You are great :) – user1935007 Dec 31 '12 at 03:39
  • @user1935007 Excellent, I have edited the answer for making the right syntax more visible. – VonC Dec 31 '12 at 09:21