I have a excel sheet, I need to use a if statement and write range activity.

If the first 4 numbers in the Number column are greater then 999 and less then 2000 then write general in the fund column. I have attached a excel sheet as a example. The numbers are formatted like this. 1234.654.564.381011.000.000. I only have to check the first 4 numbers so in the example just check to see if 1234 is great then 999 or less then 2000 if that is true then write general in the fund column. Please see attached sheet.Book1.xlsx (9.9 KB) Thanks for the help

If the first 4 numbers in the number column are greater then 999 and less then 200 then write general in the fund column

In the excel sample the first number is 4004.000.000.381011.000.00000. 4004 is greater then 2000 so nothing would happen. the second number in the number column of the excel sheet is 1000.000.000.311010.000.00000, 1000 is greater then 999 and less then 2000 so General should be written in the fund column.

Any Ideas?

User **Read Range** to read the Excel file into a datatable. Use **For Each Row** to loop through all the rows. In the loop you can read and convert the first four digits like this:

```
FourDigits = CInt(row("Number").ToString.Split("."c)(0))
```

Use **If** to compare it and then you can write to the βFundβ column like this:

```
row("Fund") = "General"
```

Use a **Write Range** after the For loop to save the changes back to your Excel file. Make that you have checked the AddHeaders property.

Where do i write this code βCInt(row(βNumberβ).ToString.Split(β.βc)(0))β

In the **For Each Row** loop:

You need to create a variable of type Int32 and then use Assign with the CInt code to convert it to a number. Then you can compare it in the If activity like this:

Thanks Brotha Nice work

You are welcome!

What if i wanted to check the next column of number? For example 2274.000.000.343130.000.50000. what if i wanted to check the number 343130 in the number instead of the first 4 we did earlier?

What if i wanted to check the next column of number? For example 2274.000.000.343130.000.50000. what if i wanted to check the number 343130 in the number instead of the first 4 we did earlier?

Just use `CInt(row("Number").ToString.Split("."c)(3))`

instead. The code split at the periods, so it looks like this after the split:

Index: 0, Value: 2274

Index: 1, Value: 000

Index: 2, Value: 000

Index: 3, Value: 343130

Index: 4, Value: 000

Index: 5, Value: 50000

`CInt(row("Number").ToString.Split("."c)(Index))`

You can use simply a method split like this say str1 is a variable contains value:

2234.4345.3232.345345

str1.split(".βC)(0) - This will give 2234

str1.split(β.βC)(1) - This will give 4345

str1.split(β.βC)(2) - This will give 3232

str1.split(β."C)(3) - This will give 345345

Once value is extracted from string convert to integer to check whether value lies in range or not.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.