Posts by Ger Plante

Received - thank you so much for the quick payment. Please shout back if any problems and I will look into it.

Gerard

Hiya, this seems to do the trick for a formula to hit the markings you mentioned:
=$A3 & REPT(" ",9-LEN($A3)) & $B3 & REPT(" ", 14-LEN($B3)) & $C3 & REPT(" ",14-LEN($C3)) & $D3 & REPT(" ", 43-LEN($D3)) & $K3 & REPT(" ", 4-LEN($K3)) & $L3

If you for some reason you think that the values will be WIDER (number of characters) than you are expecting in the given column, for example someone entered 10 characters into Column A, instead of 9, then the formula will return #Value, which seems like it would be correct (i.e. something you want to manually investigate). However - if you want to allow "bad incoming data" then you have two choices as follows:
1. Truncate the value in the column to the specified or allowable data format width - this formula will do that, but it might be dangerous for things like Bid Price/Ask price getting truncated. This one is a little messy.
=LEFT($A3,9) & REPT(" ",MAX(0,9-LEN($A3))) & LEFT($B3,14) & REPT(" ", MAX(0,14-LEN($B3))) & LEFT($C3,14) & REPT(" ",MAX(0,14-LEN($C3))) & LEFT($D3,43) & REPT(" ", MAX(0,43-LEN($D3))) & LEFT($K3,4) & REPT(" ",MAX(0, 4-LEN($K3))) & LEFT($L3,4)

2. Accept / allow more characters than are allowed in the data format and shove everything else to the right, then this formula will allow this. This might "break" your downstream use of the resulting cell data, as it may break your data format rules
=$A3 & REPT(" ",MAX(0,9-LEN($A3))) & $B3 & REPT(" ", MAX(0,14-LEN($B3))) & $C3 & REPT(" ", MAX(0,14-LEN($C3))) & $D3 & REPT(" ", MAX(0,43-LEN($D3))) & $K3 & REPT(" ", MAX(0,4-LEN($K3))) & $L3

The VBA implementation and equivalent of the very first formula above can be done in a custom function that you call from Excel (assumes the data is in good order, and ignores character width issues I mention above))

Public Function My_Concat(r As Range) As String


If r Is Nothing Then Exit Function
on error resume next


My_Concat = r.Cells(1, 1) & Space(9 - Len(r.Cells(1, 1))) & _
            r.Cells(1, 2) & Space(14 - Len(r.Cells(1, 2))) & _
            r.Cells(1, 3) & Space(14 - Len(r.Cells(1, 3))) & _
            r.Cells(1, 4) & Space(43 - Len(r.Cells(1, 4))) & _
            r.Cells(1, 11) & Space(4 - Len(r.Cells(1, 11))) & _
            r.Cells(1, 12)
    
End Function

You would call it thus from a cell, pointing at the range of cells that needs to be concatenated:
=My_Concat(A3:L3)

If needed I can implement into a traditional Macro too that you call from the Macro menu, but to be honest, the Function is far cleaner

Ger

Grand... will have it for you tomorrow at some point. Just going to bed

When do you need it?

Do you actually want to delete the rows, or just "hide" them...?

Edit: Sorry KjBox, go ahead. I'm too slow... as usual

I cant personally help, but similar-ish topics here around com ports in the following links. Maybe of some help.

https://www.ozgrid.com/forum/f…to-connect-to-serial-port
https://www.ozgrid.com/forum/f…-com-serial-port-to-excel
https://www.ozgrid.com/forum/f…ata-read-from-serial-port
https://www.ozgrid.com/forum/f…elp-excel-vba-serial-comm
https://www.ozgrid.com/forum/f…ing-serial-communications
https://www.ozgrid.com/forum/f…5765-serial-port-problems

Ger

Just to be clear - you need to understand the logic flow here...

1. Your original sheet must have the Additional Sheet Button AND a delete button
2. Hide the Delete button by changing its .visible property to false in design mode so that no one ever sees the delete button the original page.
3. When the additional page button is pressed, create a copy of the page (the active sheet at that point in time). This copies the sheet, its contents its TWO buttons (hidden and unhidden) and code associated with the buttons
4. The copied sheet becomes active, so now HIDE the additional button sheet (becuase you dont want an additional page button on each additional page - presumably?), and UNHIDE the delete button.
5. When someone presses the delete button on an additional page, just delete the page (this will delete the buttons too).

Code below... and attached also.

Private Sub DeletePage_Click()
'delete
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    
    ActiveSheet.Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub


Private Sub AdditionalPage_Click()


    Dim sht As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    
    ActiveSheet.Copy after:=ActiveSheet
    ActiveSheet.Shapes("DeletePage").Visible = True
    ActiveSheet.Shapes("AdditionalPage").Visible = False
    
End Sub

Awesome sauce... very very quick for me on a 2D x 1 column sort with numbers. over 40k rows.. suggestion for improvement... add another optional parameter for ascending or descending... not sure how badly that would screw up the code. Or maybe include instructions to convert between one and the other. I guess its a case of changing all < with > ? Thanks mate.

Ger

I am presuming the additional page is just a copy of the original page with the "additional page" button

If so, just ensure you keep the button hidden at all times on the original page... you can set this with code, or at design time by accessing its properties

ActiveSheet.Shapes("CommandButton1").Visible = False

Where you have the code to copy / create the additional page, AFTER the page is copied (because after you copy the sheet everything is copied and the copied sheet becomes the active sheet):

ActiveSheet.Shapes("CommandButton1").Visible = True

Ger

Hmmmm... wish I had seen this yesterday... LOL. I’ll try it out later.

Thx trunten...

i knew about $ from 30 years back when I was learning basic on my amstrad.. and picked up on & more recently. but didn’t know the rest. Thx for the tips.

Its hard to say if this is the correct answer, because dont know what its supposed to be, but my guess is that you are getting a stream of zero's for "Annual GLWB Payout" where no zeros were expected

There are a few things wrong that I would suggest changing.

The arrays you defined are as follows (I dont know if this is the case in your real code, so forgive me if its unnecessary):
Dim issueAge(4), waitPeriod(4), annuitizationAge(4), deathAge(4) As String

This means that IssueAge, WaitPeriod, AnnuitzationAge are all arrays of type VARIANT. The final Array - DeathAge is an array of type STRING. My guess is you intended all arrays to be of type string. This is a common mistake made by new VBA'ers and it differs from say Java... where you could list a number of comma separated variables and a variable type.... not the case in VBA, you have to be explicit about each indiviual variable... for example
Dim x as string, y as string is OK, but Dim X, y as string means X is a variant and Y is a String...

(I Dont meant to be patronising when I say "new VBAers", but you'd be surprised the number of non-VBA "experienced" developers who dont realise or understand this behaviour).

Next, leading on from this, and more importantly, why would define a string array to store Integer or single values? I believe you should define them as:
Dim issueAge(4) as single, waitPeriod(4) as single, annuitizationAge(4) as single, deathAge(4) As single (or Integers if you wont be storing any decimal values)

However, doing this now means you have to typecast the values from the userform to ensure they are converted to actual values... e.g.
issueAge(2) = Val(exampleForm.tbIssueAge3.Value) this means that if it is a blank in tblIssueAge3 it wont "fit" into an array of single or integers, so you have to use the VAL(tblIssueAge3) which will return 0 for blanks, and this can be assigned to the array of type integer

Confused yet?
The simplest solution, leaving all the above aside, if you dont want to change any of your existing variable declarations - and go straight to something that works, then simply change the offending if statement and force the correct type casting. Because in here you currently have an array of strings, and you are trying to do comparisons against numerical data and VBA will not work as expected... so this line

If (Cells(2, z).Value + issueAge(x)) < annuitizationAge(x)

is evaluating to:
1+"60" < "71"

The " mark is indicate the value is a string.

So convert your string variables to numbers with the VAL function:

If (Cells(2, z).Value + Val(issueAge(x))) < Val(annuitizationAge(x)) Then

And it seems to work just fine.

Long story short - always use VAL when converting between string or variant types into integers/singles.

Regards
Ger

Looking now... sorry for the delay, I was out of town. Thanks for the sample... be right back

Desanitise your worksheet completely and your code.

If X is a period of time entered by the user (days or months or whatever), and if you argue that replace the issue age and annage function calls with actual values will fix the problem, then the problem in my head seems to be that you are not prompting the user often enough for new values of X, or the functions themselves are incorrect.

Its hard to debug when we dont have the full picture. Its like showing a corner of a painting to someone and asking them if they like the look of the entire thing

Quote from jkov05

The z variable ranges over months and so Cells(2,z) tracks the years so, for example, after 12 years, Cells(2,149) + issueAge(0) should return > annuitizationAge(0) but it does not.
If I replace either issueAge or annAge with their values it returns correctly, but I need it to work for all X's.

Do you guys know why this is not working? Thanks!!

No where in your code snippet above, especially within the FOR loop have you changed the value of variable X. So X will only ever be the value that it is set to somewhere else in this procedure or maybe some other module. We also dont know if the function IssueAge or AnnAge are returning the correct value. We also dont know what X is supposed to represent. But for now, the value of X isnt being set or adjusted in your code snippet above, so it deifnitely wont work for "All X" as you claim you require.

Maybe post back the worksheet, with all the code, and an explanation of what the code needs to do in simple english

Ha ha, not a problem KJ - please go ahead... I may not have time to look at it this week.

Just realised the column misalignment that i thought existed is just because the weekly file has an extra column of information in Column A, a URL, which the sample file does not... so they seem to align after that. So please go ahead KJ.. I'll bow out here.

Ger

Hi GoCavs...

In summary, are you simply then overwriting all the records in the weekly report with the rows of data from the Snapshot export?
Couple of concerns around the columns... they do not align 1 to 1... for example
Column AK header in the Weekly report says "TRS Est. Billings"
Column AK header in the sample report says "Lead Opportunity Description"

It seems there is an extra header in the weekly report file.

There are also difference in the column header names... for example,
"Updated By" in the sample report seems to equate to "Last Updated By" in the weekly report.

The number of rows/formatting is not a problem.

Regards
Ger

How do we know this is not some subversive attempt to harvest IP addresses and install Malware with network / port sniffing capabilities. Sounds very suss to me... Can you post the VBA code here?

Thanks, just want to be sure I understand, you are referring to gaps, where no driver was assigned? This happened because no driver at all was available at this time... all of them were utilised.

so we would need to change the logic a bit to
1. Assign the job on first available basis.
2. If all drivers are in use for a job, then Assign a job to the next available driver (closest in time)....

Is that right? So at the end, no job should be unassigned.... ?

This might be trickier than it seems