Posts by ArtySin

Thanks VERY much Nilem, it works a treat

I have some code below which changes the background colour and capitalises the active cell. When I click on another cell the previous cell is still capitalised. Could someone please modify the code so that the previous cell isn't capitals nor bold anymore.
Many thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Target.Formula = UCase$(Target.Formula)
Target.Font.bold = True
Application.ScreenUpdating = True
End Sub

Carim,
Thanks very much, worked perfectly :yourock:

Guys,
I found some vba to hide all rows that do not have any results greater than a zero in them. For example rows 1 and 3 below should be hidden whilst rows 2 and 4 should still be visible.. [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

Test

27-Feb

15-May

27-Jul

15-Aug

20-Aug

09-Nov

14-Dec

Count

0

0

Count

0

80

0

Count

0

0

Count

40

[/TABLE]
Unfortunately, not being a coder I can't work out why it won't work. Could somebody please take a look for me? I've attached the .xlsm w/book, hope you can help.
Many thanks
ArtySin
Summary of Results.xlsm

Re: Precede last retrieved plus 1 number with a "C"

Quote from ArtySin;800231

Guys,
To retrieve the last number, plus 1, in a column I use:

=SUM(VLOOKUP(9.9E+307,Sales!C9:C999,1)+1)

which of course works fine. However, with a small change in procedure it now needs to be preceded with either a "C" or a "J" but for the life of me I can't seem to get this to work.

Any ideas at all?
Many thanks
ArtySin

Display More

I couldn't see the wood for the trees, it was as simple as:

="C "&SUM(VLOOKUP(9.9E+307,Sales!C9:C999,1)+1)

Guys,
To retrieve the last number, plus 1, in a column I use:

=SUM(VLOOKUP(9.9E+307,Sales!C9:C999,1)+1)

which of course works fine. However, with a small change in procedure it now needs to be preceded with either a "C" or a "J" but for the life of me I can't seem to get this to work.

Any ideas at all?
Many thanks
ArtySin

Re: SUMIFS only returns #VALUE!

Quote from skywriter;799944

Your named range "sales_inv" is multiple columns and needs to be a single column.

You see in the formula below that I just used A9:A300 and changed the syntax in the last part of the formula and it seems to work fine. :cheers:

=SUMIFS(inv_gross,A9:A300,U9,paid,"No",client_days_overdue,">=" &$V$7)

Well done skywriter!!!!! Always something daft that I miss :smash:.

Thanks once again :thanx:
ArtySin

Re: SUMIFS only returns #VALUE!

Thanks skywriter, file attached
ArtySin

Re: SUMIFS three ranges

Guys please ignore this message and view my newer post SUMIFS only returns #VALUE!
Many thanks
ArtySin

Guys,
I have a sheet that has named ranges as follows:

Column A are client names with multiple row instances where clients are invoiced, named range = sales_inv
Column G is the sum range, named range = inv_gross
Column L is Paid Yes/No, named range = paid
Column O is no. days overdue named range = client_days_overdue and $V$7 =60

In column V is a calculation to sumifs the total each client owes if not paid after 60 days"

=SUMIFS(inv_gross,sales_inv,U9,paid,"No",client_days_overdue,">="&$V$7)

The issue I have is that the result always returns #VALUE! I've tried with and without quotes as in the formula and also including the cell as well ">=$V$7" but with the same result. Probably missing something simple her.
Many thanks
ArtySin

I have a sheet that has named ranges as follows:

Column A are client names with multiple row instances where clients are invoiced, named range = sales_inv
Column G is the sum range, named range = inv_gross
Column L is Paid Yes/No, named range = paid
Column O is no. days overdue named range = client_days_overdue and $V$7 =60

In column V is a calculation to sumifs the total each client owes if not paid after 60 days"

=SUMIFS(inv_gross,sales_inv,U9,paid,"No",client_days_overdue,">="&$V$7)

The issue I have is that the result always returns #VALUE! I've tried with and without quotes as in the formula and also including the cell as well ">=$V$7" but with the same result. Probably missing something simple her.
Many thanks
ArtySin

Re: SUMIFS only returns zero values

Quote from skywriter;796505

Try "<=" instead of "=<" it worked for me.

Blimey, can't believe it was that simple, works great now.
Thanks very much
ArtySin

Hi,
I have a small w/book which I've attached that will not return any totals at all. There are three named ranges: date, amount and paid. The idea being that to the right of these columns there is an end of month date where it should total the amount either paid or not paid. Unfortunately all I get is zeros and having searched various sites extensively, I can't for the life of me pinpoint what is wrong.
Many thanks
ArtySin

I have a sheet with about 1500 rows and in each row there will be two or three occurences of text. This text will always start the same but with four numbers at the end which will be different. Example: SSS-DEWBU-1234 and SSS-DEWBU-3456. There will be a lot of other text in each cell, and a typical single cell example is:

SSS-DEWBU-1234
This is an example of the text in
each cell. Where the text varies.

SSS-DEWBU-3456
Further text to display the this
example of cell contents

I need to lookup each cell in the column and extract the SSS-DEWBU-1234 and the SSS-DEWBU-3456 from each cell in the column and copy it to the adjacent cell. So where there are two of these in one cell, the first will go to column B and the second to column C. I've attached a workbook with three in one cell to see what it should finish up with.
Many thanks
ArtySin

Re: RowSource value not in ComboBox properties

OK thanks

Re: RowSource value not in ComboBox properties

Yes I am but have also tried it in Windows with Office 2013

Re: RowSource value not in ComboBox properties

Quote from Kenneth Hobson;759695

It is unclear what you are trying to achieve. Is Data_entry_button a form, activex, or userform button control object? What object does ComboBox1 belong to? If you are showing Userform1, then it would not be that object.

If combobox1 is an activex control on a worksheet, then you would probably want to use ListFillRange property but not the .Value of a range. If ListFillRange is needed, I can show you how to use it or fill the control's List with values.

The "BSMs" worksheet has a button on it named "Date_entry_button" which when clicked displays "UserForm1" no problem. On this form is "ComboBox1" (I've re-named it FlightBox1) which I'm trying to populate with 20 flight destinations which are shortened to just three letters. These being:
EDI
JFK
MIA
etc...

After that I will need to copy the selected value to cell G4 on the "BSMs" worksheet but as mentioned I'm having no luck. The control is not an ActiveX control although I've also tried using one but came across the same problem.
Many thanks for your help.

I've got a very simple UserForm that has a ComboBox in it. However, it will not populate the list to display. I have tried adding the following code:

Sub Data_entry_button_Click()




UserForm1.Show




ComboBox1.RowSource = Workbooks("BSM Generator").Sheets("Lists").Range("$K$2:$K$22").Value








End Sub

This doesn't work and also in the "Properties" box there is no entry for RowSource which according to all the pages I have read is supposed to be there. Anybody any ideas on this at all please?

Guys, don't think this is possible but if somebody knows please enlighten me.

We have an emulator into which we inject data so many seconds after we start it. The number of seconds is always a decimal number as the emulator does not understand time formats. That being the case we have to write everything in both the time format which also cannot be changed as we need to know the time the data is injected and the number of seconds after the emulator started. Here's an example:

Column A Column B
Data Injects at No. of Seconds after start
11:00:00 0
11:05:00 300
11:10:00 600
11:15:00 900

Does anybody know of a way we can, for example, add 900 to 11:00:00 to display 11:15:00
Many thanks
ArtySin

Re: Copy data from cells to new row on the sheet

Quote from cytop;754438

You're determining the destination row from the Credit Notes sheet - you need that from the Refunds sheet...

My defence - it should have been obvious from reading the code, I was using a phone at the time.

My defence? None, should have spotted it... Doh!

Thanks for the eagle eyed solution cytop, all working properly now.
Much appreciated
ArtySin