Writing a variable in a formula to an excel cell


#1

Hey… I am trying to insert a formula into an excel cell to determine whether there are duplicates in a list…

however, when I use variables to represent a first and second name, it is not successful (the formula does not read the names and the formula returns 0 when it should return 1).

The formula I am trying to write in a cell is… “=COUNTIFS(B:B,” + vStrFirstName.ToString + “,C:C,” + vStrLastName.ToString + ")"
See excel “NameCheck” and UiPath “NameCheck”

Namecheck.xaml (10.8 KB)
NameCheck.xlsx (8.5 KB)

The formula is successful when I use write cell “=COUNTIFS(B:B,”“Charlie”",C:C,"“Ellis”")"
See excel "NameCheck1"
Namecheck1.xaml (10.8 KB)

I think there is an issue with my first and second name variables, are you able to help please…?

Thanks


#2

try this

"=COUNTIFS(B:B,"""+vStrFirstName+""",C:C,"""+vStrLastName+""")"


#3

Success! thank you @ddrdushy1


#4

Do I need to put anything after the formula string to make sure it calculates as soon as it is pasted into excel sheet?


#5

Send hotkey “F9”


#6

I want to paste a formula from a variable in excel and make it calculate immediately. I write the variable with a Write Cell, but it writes the variable as text, not as a formula. Sending the hotkey F9 doesn’t do anything either. Maybe anyone here has a solution?