none
VBA code to check if cell starts with a specific letter RRS feed

  • Question

  • I am trying to write a code that will determine if a cell contains a data that starts with letter "K"

    Here's what my code looks like and it doesn't work. Any help is much appreciated!

    Thanks!

    'Check if Col B are K files
     Range("J2:J" & Range("A" & Rows.count).End(xlUp).row).Formula = "=IF(LEFT(TRIM([@[File '# (remote)]]),1)="K","1","0")"
    
    
    

    Wednesday, January 24, 2018 9:19 PM

Answers

  • Your formula contains quoted strings. Since the entire formula is enclosed in quotes, you have to double the quotes around the strings:

    Range("J2:J" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT(TRIM([@[File '# (remote)]]),1)=""K"",""1"",""0"")"

    although I would use

    Range("J2:J" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT(TRIM([@[File '# (remote)]]),1)=""K"",1,0)"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Wednesday, January 24, 2018 10:07 PM
    Wednesday, January 24, 2018 9:51 PM

All replies

  • Your formula contains quoted strings. Since the entire formula is enclosed in quotes, you have to double the quotes around the strings:

    Range("J2:J" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT(TRIM([@[File '# (remote)]]),1)=""K"",""1"",""0"")"

    although I would use

    Range("J2:J" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT(TRIM([@[File '# (remote)]]),1)=""K"",1,0)"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by IamJackie Wednesday, January 24, 2018 10:07 PM
    Wednesday, January 24, 2018 9:51 PM
  • Thank you very much! I used the second one to minimize the quotes. :)
    Wednesday, January 24, 2018 10:07 PM