I was able to extract the data I wanted which is the variable taxi distance. However, when I write cell, it shows the error of this topic.
The expected result is that the taxi distance (in yellow) will be filled, as well as other figures that will match according to the month.
Please mention the cell value properly.
Try to print the cell value
excelRange.Substring(0,1)+Index in a message box and check if you can get the correct output.
An Object Reference error normally means the variable you’re referring to is empty or not initialised. Put a breakpoint in your code, run your code in debug and check the Locals panel see which variable is empty. I imagine it’ll be either your cell value as you haven’t included it in your screenshot, or your LookUp Range activity as you’ve not specified a range to lookup against.
Once check what is printing the excelRange.Substring(0,1) in message box
My excelrange, which comes from my lookup is null
I actually get the same error for the message box as well
Is there a specific range you can use on your Lookup? You could try specifying one and seeing if that changes excelRange’s value.
so the cell value is not correct please correct the cell value syntax.
Does the excelRange variable contains the expected value. Try to print the excelRange varaible in a message box.
What are you look in look up activity can yous how…looks liek its not returning anything or is not able to find the value you want to lookup
Hi Anil, I am trying to look up based on my variable called input excel, which gives me Apr’ 23. Based on this month and year I extracted, I want to lookup the specific then write the distance that I have also extracted into the month I found
Could it be a difference in the position of
’ in the months?
Are you using excel look up?
if so in excel Apr’23 might have a different value as well like fulldate…check by opening excel manually or by reading the excel data into a datatable…
Yes Anil I am trying to use the lookup function. This is how my date is formatted in excel.
I do think that there is something wrong with my lookup activity though as the output of the lookup I placed in the message box (excelrange) is blank
Having excelrange blank is the issue right…you should be getting a cell value I believe…
How you format and how the data is present in the cell might be different is what I am trying to say…
Hi Anil you are right,
I found out that this is my excel cell is formatted by MM/DD/YYYY hh:mm:ss
But I am not sure how to change my extracted data to this format
If you would require to write on that particular column, you would need the Excel Column Letter
Z and not the Column Header name as you are directly writing in Excel cell.
So the Range that you would need to get is
Could you let us know how are you getting the Column Name
Apr'23, we can modify that to get the required Excel Column Letter.
Let us know if the above is understandable and if it is the step required.
From where are you extracting it? Are you not getting the complete date? …time I believe is 00:00:00 always
I get actually get the month from text/left right on some invoices.
In this format
However, in the excel I was provided, the month of column z is in this format after writing line 04/30/2021 00:00:00. At the point of my post I only tried to change the format to what I saw in the excel which was Apr’ 23.
I’m not sure if this information is helpful to you
Could you also show us How the Excel Range is calculated ? And Does the Excel Range value give null as value ?
I believe I may have filtered out the full date using text left right activity, I have since retrieved it back and it is now in DD/MM/YYYY format
@supermanPunch Sorry, do you mean the spreadsheet itself, or the variable? The variable is just the output of my lookup function and it returns null.
Here is my current workflow and a sample of the excel I have so you can see how my workflow roughly works
DelGro.xaml (15.1 KB)
Sample.xlsx (11.4 KB)