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!
Oh, I looked at your picture!
Unfortunately, we have a typical “XY problem” here - you asked for a macro from the very beginning and we tried to make a macro for you.
In fact, you need specific values from table A14:D41, which are easy to obtain using not very complex formulas and writing a macro is the way you came up with because you didn’t know how to make these formulas.
Let’s put the search texts you labeled “text to find 1”, “some text to find 2”, etc., in cells H7:Q7. If you don’t want to see them there, that’s not a problem, there are many ways to hide them (white font on white background, cell format code ;;; and a few others).
Now in cell H8 write =IFERROR(VLOOKUP(H$7;$A$14:$D$41;4;0);NA())
And in cell H10 write =IFERROR(VLOOKUP(H$7;$A$14:$D$41;3;0);NA())
These formulas differ in only one digit (4 or 3) - the column number in table A14:D41
Now format these two cells the way you want (as Currency justify right).
Copy this, highlight the I8:Q10 range and paste.
I assure you - it is much easier and faster than writing a macro, debugging it and making it work correctly.
Hmmmm you misunderstand me!
To clarify:
in column A14:A41 I have text 27 different texts. I am interested for 11.
in column D14:D41 I have values
in column E14:E41 I have another values
If I found in A15 “text 1” then copy value from D15 to H8 and copy value from E15 to H10
if I found in A17 “text 2” then copy value from D17 to I8 and copy value from E17 to I10
Every day I get file where are strings in column A, but not every time in same row. That is the problem.
I need to find 11 string and copy total of 22 values.
So copy values from column D to row 8 and values from column E to row 10 from column H to Q
=INDEX($E$14:$E$41;MATCH("SomeText 1";$A$14:$A$41;0)) find text ind write value in cell.
I need that values in H8:Q10 because I copy this to another document.
I hope that this is understandable.
Hmmmm you misunderstand me!
To clarify:
in column A14:A41 I have text 27 different texts. I am interested for 11.
in column D14:D41 I have values
in column E14:E41 I have another values
If I found in A15 “text 1” then copy value from D15 to H8 and copy value from E15 to H10
if I found in A17 “text 2” then copy value from D17 to I8 and copy value from E17 to I10
Every day I get file where are strings in column A, but not every time in same row. That is the problem.
I need to find 11 string and copy total of 22 values.
So copy values from column D to row 8 and values from column E to row 10 from column H to Q
=INDEX($E$14:$E$41;MATCH("SomeText 1";$A$14:$A$41;0)) find text and write value in cell.
I need that values in H8:Q10 because I copy this to another document.
I hope that this is understandable.
You don’t believe me. You have the right to doubt. But try it anyway. Here is a sample file.
Search specific data.ods
Pretty much correct.
Let me know how to attach image and how file.ods
Oh, it’s very easy. Go back to your question, under it there is the word “edit” - click on it.
Thank you!
I edited my question and rewrite basic code. Also add sample and image.
To put formula in cell is not a problem. I would like that macro does this for me. Not put formula to cell. Just read value from one cell and write value to another!
It need to written in LO basic language!
Why can not add image/file to comment?
P.S. How to add CR in comment (chr$10+chr$13)?