MS Excel / VBA help thread

Would this help?
https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

It looks like you could time different parts of your calculations using the stuff in there.

I mean. I COULDN'T.

Going RoUnd In Circles. . .

I want 2 Sum's For Column C

1st ) Where The Contents Of The CellS In Column D = The Letter C
2nd) Where The ContEnts Of The Cells In Column D = The LettEr B

=IF(D5="C",C5, 0) giVes Me The Value Of JusT The One Cell In Column C When The coresponding Cell In Column D = C

. . . But How To I Get The sum Of All Of Cells In The Column WHen ThEY = C

?

I Thought: =IF(D5:D16="C",C5:C16, 0)
but No Luck .

(sorrY For The Funny Writing , swift KeY Has Gone Nuts)

Or alternatively I feel like I'm almost their with

=SUMIF=(C4:D16, D4:D16="C", C4:C16)

But that returns 0

=SUMIF(D4:D16,"C", C4:C16)

or sumifs() if you need to add the 'C's and 'D's together (assuming excel version >2010)

Cheers.

hello
I have a long word doc that i want to split into seperate documents, each three pages long (it was a mail merge).
can someone write me a macro to do this?

This should probably do it

I just nabbed it from here https://www.extendoffice.com/documents/word/966-word-split-documents-into-multiple-documents.html#a1 and changed it to three pages though so no promises.

Sub SplitIntoPages()
Dim docMultiple As Document
Dim docSingle As Document
Dim rngPage As Range
Dim iCurrentPage As Integer
Dim iPageCount As Integer
Dim strNewFileName As String
Application.ScreenUpdating = False 'Makes the code run faster and reduces screen _
flicker a bit.
Set docMultiple = ActiveDocument 'Work on the active document _
(the one currently containing the Selection)
Set rngPage = docMultiple.Range 'instantiate the range object
iCurrentPage = 1
'get the document's page count
iPageCount = docMultiple.Content.ComputeStatistics(wdStatisticPages)
Do Until iCurrentPage > iPageCount
If iCurrentPage = iPageCount Then
rngPage.End = ActiveDocument.Range.End 'last page (there won't be a next page)
Else
'Find the beginning of the next page
'Must use the Selection object. The Range.Goto method will not work on a page
Selection.GoTo wdGoToPage, wdGoToAbsolute, iCurrentPage + 3
'Set the end of the range to the point between the pages
rngPage.End = Selection.Start
End If
rngPage.Copy 'copy the page into the Windows clipboard
Set docSingle = Documents.Add 'create a new document
docSingle.Range.Paste 'paste the clipboard contents to the new document
'remove any manual page break to prevent a second blank
docSingle.Range.Find.Execute Findtext:="^m", ReplaceWith:=""
'build a new sequentially-numbered file name based on the original multi-paged file name and path
strNewFileName = Replace(docMultiple.FullName, ".doc", "_" & Right$("000" & iCurrentPage, 4) & ".doc")
docSingle.SaveAs strNewFileName 'save the new single-paged document
iCurrentPage = iCurrentPage + 3 'move to the next page
docSingle.Close 'close the new document
rngPage.Collapse wdCollapseEnd 'go to the next page
Loop 'go to the top of the do loop
Application.ScreenUpdating = True 'restore the screen updating
'Destroy the objects.
Set docMultiple = Nothing
Set docSingle = Nothing
Set rngPage = Nothing
End Sub

Cheers, I may do some experimenting at some point. I kind of knew it wouldn't be as simple as something saying each cell takes x second to calculate as there are various dependencies.

It's hard. In the past I've written vba that gradually hard codes (paste special -> values) columns (or row, whatever) of the used range, one by one. After each hardcode, recalculate with a timer around it and time all the sections. Use something like Ken Getz's stopwatch class from the VBA Developers Handbook to time it.

You can kind of pinpoint which area is the slow one, then analyse that area / formula in more detail. It gets you close, but it's not great and quite a bit of work.

Odd one. Moving data from a csv in one citrix session into an xls in another citrix session so I can analyse using pivots and vlookup, but more importantly save & share the data (can't on first citrix, systems eh).

When done the date field gets muddled from ddmmyyyy to mmddyyyy format.

This means also the biggest value is 12/12/yyyy ie there are no day values 13-31 which is statistically improbable given that I'm looking at 5000+ customers last visit dates for a venue open 364 days a year.

Is there any pre-work (formatting) i can do in the first citrix session to ensure I get the correct data across to the xls file in the second citrix session?

Caveat - it might also be a flaw in the database I'm reporting on, however one customer I've checked on both the csv & xls has a last visit 28/10/16 (csv) which ends up showing as 10/12/2016 on the xls.

Confused? Me too!

It's flipping between the formats - possibly as the first citrix thinks it's American. Dates can be very annoying in excel when they do this kind of thing.

You can try fiddling with the cell formats (ctrl-1 on the source column) on the source excel session and changing between different date formats, maybe even string formats since they're being exported via a csv.

You can probably add a column to convert the source date to an integer (where the formula in the new column is - 0). When these come into the second citrix session, convert that column back dates (ctrl - #).

Possibly the easiest might be to change the date format on the source session to something unambiguous like dd-mmm-yyyy (again, ctrl-#). I imagine it would be exported into the csv like this then automatically detangled in the second excel session.

If you open the CSV, it becomes an XLS instead, adopting the system and excel preferences of the environment in whichnis being opened.

When you save the CSV, make sure that the whole document is formatted as text.

Rule 1. Never open csv files in excel.

Yeah this.

Cheers folks. I think I have to look at the formatting as per the above as I'm copying and pastin the data from citrix 1 (csv) to citrix 2 (xls). I've no way to save the data in citrix 1 to a shared drive so I can then open it in citrix 2, not even via host pc.

P.s. Could pasting into notepad then re-copying from there help?

fist pump

Rule 1. Never open csv files in excel.

Opening the csv in notepad then copy & paste has worked.

fist pump

This.

In my excitement I forgot to add huge thanks folks!

Edit
Also forgot i used data import from text rather then c&p, import wizard gave me 3 options on how to treat the date values. Ideal.

I have a question, hahahaha, my find replace array is working well. I've noticed one user is populating a lot with
RECx
where x = a number

My find replace array thing currently has REC3 and REC2, but I don't want to write out REC1-REC1000 and replace with 1-1000.

How can I say REC* and then in the replace column just have the number?

sorry for making no sense.

can you post the find replace code (is it vba?). Sounds like a job for a loop or wildcard

but I don't want to write out REC1-REC1000 and replace with 1-1000.

Also, this. Don't do this, but if you ever do find yourself having to write many (maybe thousands is excessive... ) of lines of very similar vba, you can set up a formula to actually write the vba for you on the worksheet.

In this case it would be a formula with your line of code in with "...." & A1 & "....", where A1 -> A1000 contains the numbers you want in the vba. You then paste the whole thing into your vba. Does that make sense?

It's utterly filthy but takes seconds, I'll admit to having done it on occasions :)

 'Create an Array out of the Table's Data
  Set TempArray = table.DataBodyRange
  myArray2 = Application.Transpose(TempArray)
  
'Designate Columns for Find/Replace data
  Find = 1
  Replace = 2

' define rng
LR = Range("A:fb").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set rng = Range("o2:fb" & LR)


'Loop through each item in Array lists find and replace
     For x = LBound(myArray2, 1) To UBound(myArray2, 2)
       rng.Cells.Replace What:=myArray2(Find, x), Replacement:=myArray2(Replace, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    Next x

before that i've told it that table is a table on another workbook.

the code opens both workbooks i need (the one with all the replacements to make and the one where the replacements are going).

What i've found is the person entering the data is trying to help. it's for a school, i thought they only had 5-11 year olds, they have reception as well. so they're tagging cases rec4 but not being consistent (it's rec:4 or Rec or REC 4). I'm updating the tool they need so that all schools with 5 year olds now have pre recept and recept years. Which is more the normal situation.

in the mean time...