Hello all,
I have a simple spreadsheet of data with values such as
1
1.1
1.2
2
2.2
2.3
3
I Would like to Find the values without decimal places and highlight them
the code i have so far is
but i get a run time error 1004 on line 1
Please Login or Register to view this content.
my questions are
is this code suitable?
what could be causing the runtime error?
Last edited by Johndotcom; 05-15-2024 at 06:06 AM.
Maybe usePlease Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Full code (untested):
Please Login or Register to view this content.
It may give an error since "2.1" can be a string, depending on the system and then Int(cell.Value) gives an error.
Hello Trevor - Manchester is a Great City, was there a couple of years ago and loved it.
As for the suggestion, thank you, this is a better method but now I would like to elaborate further
when I find the range without the decimal I would like to add a new row.
I am using this at the moment
however, it stops at the first found range without the decimal and does not pass and go to the next cell.Please Login or Register to view this content.
I was because Range A1:A contained a # and blank so i changed it to
Please Login or Register to view this content.
You're welcome.
Sharing a sample workbook (in future) helps mitigate issues that cannot be seen or guessed at
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Hello Trevor - Manchester is a Great City, was there a couple of years ago and loved it.Originally Posted by TMS
As for the suggestion, thank you, this is a better method but now I would like to elaborate further
when I find the range without the decimal I would like to add a new row.
I am using this at the moment
however, it stops at the first found range without the decimal and does not pass and go to the next cell.Please Login or Register to view this content.
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
To my understanding this should help.
Like when deleting rows you start at the bottom and work your way up. Same principal here.Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
@bakerman2: I agree that you should start from the bottom as the range doesn't auto-adjust in the loop, so you could stop too soon. However, I want to see the file as I don't understand why it would stop after the first cell.
Trevor, just did the test and it keeps adding rows after cell A1. I stopped the code and when I stepped through the code with F8 my lastrow suddenly was 20000+.
Like you said yourself the range doesn't auto-adjust in the loop.
To my understanding this should help.
Like when deleting rows you start at the bottom and work your way up. Same principal here.Please Login or Register to view this content.
You have understood what I've asked for and it works like a charm, if you could see the size of the program I'm developing you would understand why its difficult for me to send the files. thanks all.
Thank you all for your advice, i can take information from all of you that makes my work much better,/
The Plot has thickened and I need your help again on this topic, therefore I have added two excel workbooks that shows what I start with and what I would like to end with
the finished code looks like this now, however it fails when presented with numbers such as 4.1.1
I'm a little perplexed buy this problem, I wounder if you are able to find the solution. I thank you all again.Please Login or Register to view this content.
Since I'm using comma as decimal sign I'm getting correct results when running code.
Maybe try something like this.
Please Login or Register to view this content.
What if you use Instr?
You should not need both the full stop and the comma.Please Login or Register to view this content.
Experience trumps academics every day of the week and twice on Sunday.
If from top to bottom try like this, another way you can using typename(cells(j,1).value), or varType(cells(i,j).value)
Please Login or Register to view this content.
Last edited by daboho; 05-16-2024 at 08:13 PM.
"Presh Star Who has help you *For Add Reputation!! And mark case as Solve"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks