Did you try out the modified macro?
Just trying but unfortunately I got an error.
needless = Array( ← this line is to long for basic editor. My strings are much longer than 6 letters.
Array Needles must be in one row or can be each argument in new row?
needless = Array(“Alfa”, _
“Audi”, _
etc…
Macro is kind of working!
I solved problem with needless = Array
Find new problem :
strings in array are treatment as the same:
"250 g, Zlata palica; čistina Au 999,9/1000 ", =
"50 g, Zlata palica; čistina Au 999,9/1000 "
AND
"31,1035 g, Zlata palica; čistina Au 999,9/1000 ", =
"5 g, Zlata palica; čistina Au 999,9/1000 "
I am (again) having a hard time understanding you, but i’ll make an educated guess.
Currently the search is looking for substrings (case insensensitiv) in the cells. Do you want the exact complete cell content to match the searchstring?
I solved that problem too :))
Macro is working and thank you both of for your kind help!
Best regards
Yes I’m looking for exact complete cell content.
Solved with exit for in second loop
For i = startrow To endrow
rem For j = LBound(needles) To UBound(needles)
For j = 1 to 11
Set oCell = oSheet.getCellRangeByName(searchcol & i)
if ( InStr(1, oCell.String, SomeText(j), 1) > 0 ) then
oSheet.getCellByPosition( putcol, putrow+0 ).String=oSheet.getCellRangeByName("E" & i ).String
oSheet.getCellByPosition( putcol, putrow+2 ).String=oSheet.getCellRangeByName("D" & i ).String
putcol=putcol-1
**exit for**
endif
Next
Next
Uf how to use this editor???
What editor? Do you mean inside of the spreadsheet?
Editor inside forum. I’m not able to format like you did my previous post.
if ( InStr(1, oCell.String, SomeText(j), 1) > 0 ) then
oSheet.getCellByPosition( putcol, putrow+0 ).String=oSheet.getCellRangeByName("I" & i ).String
oSheet.getCellByPosition( putcol, putrow+2 ).String=oSheet.getCellRangeByName("H" & i ).String
putcol=putcol-1
exit for
endif
How can read those cells as Currency?
It was a bit tricky to change the format code,
luckily the macro bibel hat some good examples.
The answer is updated to now include some code
to modify the format code of the target cells.
You still have to change the format code to fit your needs.
As en example i used "#,##0.00 [$EUR];-#,##0.00 [$EURO]"
Thank for your effort.
I rewrite code a little bit and now is working as I like!
:))
except this minor issue.
“Do you want the exact complete cell content to match the searchstring?”
Yes I would like use MATCH function somewhere in loop:
For j = LBound(needles) To UBound(needles)
Set oCell = oSheet.getCellRangeByName(searchcol & i)
if ( InStr(1, oCell.String, needles(j), 1) > 0 ) then
rem copy value and change cell format
Those two strings are treathed as same!
"250 g, Zlata palica; čistina Au 999,9/1000 ", = "50 g, Zlata palica; čistina Au 999,9/1000 "
AND
"31,1035 g, Zlata palica; čistina Au 999,9/1000 ", = "5 g, Zlata palica; čistina Au 999,9/1000 "
Well that is kind of strange, these 2 Strings should in no way be considert equal.
Maybe the InStr description can help you.
When I step over in Basic editor I can not get string variable for oCell.String and needles(j) !
I tried InStr without start, mode and mode 0 parameter but it does not help.
You have to “expand” the “oCell” variable to view it properties (e.g. String) by clicking on the arrow / rectangle beside the “oCell” in the variable display.
Thank you for explanation!
oCell.String is SAME for needles(2) and needles(4) !
needles(2) should be “250…” and needles(4) should be “50…”
but in loop oCell.String for needles(4) is “250…”
I copied both strings an inserted them into the A columsn, than used the "250 g, Zlata palica; čistina Au 999,9/1000 " as the needle element.
I only get one, here my “output” aka. “result” after running the DOSTUFF subroutine
Found a problem in statement
if ( InStr(1, oCell.String, needles(j), 0) > 0 ) then
if ( InStr(1, oCell.String, needles(2), 0) > 0 ) then
and
if ( InStr(1, oCell.String, needles(4), 0) > 0 ) then
in same row (A18) are both true which is wrong
Could you please post an ods with your subroutine, so i can see / review your issue. Everything else is not very efficient.
Now i see what the problem is.
Simply replace
if ( InStr(1, oCell.String, needles(j), 0) > 0 ) then
with
if (StrComp( Trim(oCell.String), Trim(needles(j))) = 0) then
StrComp is more fitting than InStr and Trim takes care of any trailing and preceeding spaces
That should help.
Thanks again for your help!
Changed line from:
if ( InStr(1, oCell.String, needles(j), 0) > 0 ) then
to
if ( InStr(1, oCell.String, needles(j), 0) = 1 ) then
and now works correct!