Posts by B.W.B.

This thread is also posted at: https://www.excelforum.com/exc…-based-on-fuel-level.html

Hello dear OzGrid, me again.
[ATTACH]n1198637[/ATTACH]
Need to find the right certificate to print based on fuel level at some date.

If i had 0 litres in reservoire and then 26Jan i received 30 000 litres, so in next days, if there isn't any other receive(in) i will print that(30000) certificate.
I need formula to somehow monitor fuel level. If fuel level is 31 000 litres at January 27th, something is wrong. If fuel level is 29 or less, then give customer that (30000) certificate. It's too complicated. Reservoires are 100 000 litres capacity, so there might be 130 000 receive in for some date(28 Jan), because, at 28 Jan i could also sell 30 000litres. It's so complicated.

Any help would be greatly appreciated.

It is great CARIM, i see why G4 isn't input cell 'cause it becomes G(filtered row number).

This is super great. Almost. When i enter value in B1 it is filtering and adding EAN to A1, when i delete B1 it also deletes A1, THIS IS GREAT. But vice versa not working. When i enter A1 EAN, B1 is empty,

I'm asking too much, sorry. Sometimes brain not working:)
Please, can I get, when I enter value in A1(b1) after ENTER jump to cell G4? After ENTER in G4, jump back to A1(b1), is this science-fiction? Thank you, God Bless!

Yes Yes Carim, great! Would be great with B1 also. So, if A1 is blank then when I type ID in B1 please, dear VBA, populate A1 with matched EAN, vice versa also.

Excel file: [ATTACH]n1196409[/ATTACH]

:sheep:

Hello, the great OzGrid!

How to automatically filter range based on one cell value?
Let's say I have empty cell A1, dear Excel do nothing, do not filter range A2:A1048576.
If there(A1) is some number then as I type in, do filtering on A2:A1048....
If there(A1) is text or space(" ") display error, if is empty unfilter range-sort AZ column A2:A1048.....

Thanks!

P.s. Range A2:G1048.... will be constant, i will not delete rows, i just need to find(filter) row based on A1 search and then enter some data in G2 address.

Also asked on: https://www.excelforum.com/exc…ed-on-one-cell-value.html

Solved.

Function Add_Numeric(iRng As Range) As Long
'ovo je za zbrajanje svih brojeva i textaBROJtext
    For Each cell In iRng
        l = Len(cell)
        If l = 0 Then GoTo nextcell
        numstr = ""
        Mf = 1
        If InStr(1, cell, "-") Then Mf = -1
        For i = 1 To l
            If IsNumeric(Mid(cell, i, 1)) Then numstr = numstr & Mid(cell, i, 1)
        Next i
        Add_Numeric = Add_Numeric + numstr * Mf
nextcell:
    Next cell
End Function

Is there a way to short this neat formula: =SUMPRODUCT(ISERR(SEARCH("trump";A2:A21))*N(+C2:E21)*ISERR(SEARCH("white house";A2:A21))) it's giving me sum of all sales wich aren't Trump and White House.

Why this formula isn't working:
=SUMPRODUCT(ISERR(SEARCH({"trump","white house"},A2:A21))*N(+C2:E21))

P.s I forgot, new look is nice, please add some green color

It's ok, but I cannot use helper cells, i must go with Search({"trump","white house"} problem is that it0s isn0t working, actually SUMPRODUCT isn't working

I must use Search("trump" because, sometimes there will be trump or Trump or TRUMP so this $A$4:$A$23=$H$1 will not work

[ATTACH]n1193762[/ATTACH]

Hello dear OzGrid!

I have this formula: =SUMPRODUCT(ISERR(SEARCH({"TRUMP";"WHITE HOUSE"};A2:A21))*N(+C2:E21)) wich should sum all values from C2:E21 wich aren't TRUMP and WHouse, but this isn't working, it only search for TRUMP. N part is for text accuracies if there's any. Maybe SUMPRODUCT can't handle Search.
This only works like this: =SUMPRODUCT(ISERR(SEARCH("TRUMP";A2:A21))*N(+C2:E21)*ISERR(SEARCH("WHITE HOUSE";A2:A21))) but is there a way to shorten this, would be great! Thanks!

Re: Extract numbers from text?

Oh my God! Now again everything is NOT working! Sum of A1:A9 and A1:D9 is not correct;(

Here is 23:47 i'm gone for zzZZZzzzz

Re: Extract numbers from text?

Bruce, my apologize, now I have completely another problem/question. Maybe I should start a new thread. Code is fine everything is fine, but, now I realize that my problem is with a column not in array(A1:D9). Add_Numeric isn't working on A1:A9 it always returns values from whole array. I thought my problem is in negative numbers, now I realize that this NUMERIC code is only for array, I wish it could work on column also:(

Re: Extract numbers from text?

No, I want strip cell to 1234, and then sum cells. Problem is with minus1234
See my workbook, there is NO ERROR when using ones(1) number one...

Re: Extract numbers from text?

I sir!

Now, with ones(1), it's odd, strange, there is no error

Re: Extract numbers from text?

Yes, it does, it's summing. =Add_Numeric(A1:D9) Problem is in negative numbers, when it see minus(-) it goes with #VALUE! error, maybe thinks it's a text?

Hello!

I have a range, let's say, A1:D9, in which are values like: "", sometext1234, 1234text, -1234, 1234, tetxt1234tetxt...
I have this code which is working perfectly but, problem is in that MINUS1234. I just want to Sum these numbers. Code is working only if num is positive

Function Add_Numeric(iRng As Range) As Long
For Each cell In iRng
    l = Len(cell)
    If l = 0 Then GoTo nextcell
    numstr = ""
    For i = 1 To l
        If IsNumeric(Mid(cell, i, 1)) Then numstr = numstr & Mid(cell, i, 1)
    Next i
    Add_Numeric = Add_Numeric + numstr * 1
nextcell:
Next cell
End Function

Re: Comma decimal separator dot

Quote from B.W.B.;776741

I figured out after three months. Problem is in PrintPreview, but WHYYY MICROSOFT, WHY!

Code

'  In case there is no printing device:
         On Error Resume Next
         Application.Dialogs(xlDialogPrinterSetup).Show
         Selection.PrintOut Preview:=True

When i set Selection.PrintOut Preview:=False everything is fine, no error.
But, why! I really need print preview

Bump :([/QUOTE]

YES YES YES YES YES YESSSSSS!
PROBLEM SOLED! Thank You: heinziburli

https://answers.microsoft.com/en-us/...1-ad96face8164

The answer is: Application.CommandBars.ExecuteMso "FilePrintPreview"