Posts by jamilm

Re: Can you do this SUM across multiple sheets in VBA?

Thank you Champion!

Quote from Armando Montes;681939

Turn on the macro recorder and you will get something like this:

Code

Range("B3:F22").FormulaR1C1 = "=SUM(Sheet2:Sheet10!RC)"

Champs

please see this attached file. i used the sum function across mutlple sheets into one sheet. i was wondering if the same thing could be done via VBA.

thanks. any help is appreciated.

forum.ozgrid.com/index.php?attachment/56045/

Dear All,

I am looking for ForumRunner App of Iphone or Ipad to be able to use Ozgrid forum.

do you know if there is such app available?

thanks.

Re: Outlook to export the detail of outlook email to excel - OPEN TO DEVELOPERS

Quote from Wigi;665538

Hello

Another possibility is that 20 USD is not appropriate for the task.
I leave that decision up to you, I do not want to force you in any direction.

just to to share here that S M C Oracle Expert has provided the solution for this and is accepted by me and payment made and confirmed. (i get to call him Champion)

Re: Outlook to export the detail of outlook email to excel

Dear Administrator,

it seems no body would like to take this. please delete this thread. i flashed my 2$ down

thanks.

Re: Outlook to export the detail of outlook email to excel

no one has taken this so far. grateful if any developer would help on this. if anything is not clear or not described well, please let me know. i think it is possible to be done, if you think it is not , please let me know. it requires few formula of networkdays to be intigrated with VBA code.

Re: Outlook to export the detail of outlook email to excel

just to mention that i also paste the link into the http://www.excelfox.com/forum/…om-outlook-to-excel-1013/

this is still open.

Re: Outlook to export the detail of outlook email to excel

Thanks Wigi.

i am looking forward hearing from other developers.

10% already paid Transaction ID: 08456030S48485548

20$ USD for imporving the below code . basically the below code is exporting the email information to a excel workbook.

Code pasted below only exports the data to excel. what i aim the desired code to do is the following

once the data is exported in excel then in excelsheet each Subject to be considered as a task and basically what i want to measure regularly that that until the each subject flag status is completed from the time that an email for a subject was received, whether it is flaged or not. it shall show the gap of the working hours between the time the subject email received and the last communicated email from the following 7 email account.

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
"[email protected]"[/email]

now, working hours is from 9:00AM to 5:00PM everyday excluding saturday and sundays which are weekends. also if there would be possiblity to add code so that i could manually put some ad hoc holidays.

so basically the macro to export only per subject and based on the last communicated email with its flag status and and sender name/address indicate the working hour it took from the time received and the last communicated email from any of the above mentioned email accounts.

i hope that i have explained this well. otherwise, please feel free to ask, if it is not clear.

Sub ExportToExcelV2()
On Error GoTo ErrHandler
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
Dim strSheet As String
 Dim strPath As String
Dim intRowCounter As Integer
Dim intColumnCounter As Integer
Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim FolderSelected As Outlook.MAPIFolder
Dim varSender As String
 Dim itm As Object
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = True
strSheet = appExcel.GetOpenFilename("Excel Files(*.xl*),*.xl*", 1, "Select Excel File", "Select", False)
 appExcel.Workbooks.Open strSheet
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
  'Select export folder
Set nms = Application.GetNamespace("MAPI")
Do
Set FolderSelected = nms.PickFolder
   'Handle potential errors with Select Folder dialog box.
If FolderSelected Is Nothing Then
    MsgBox "There are no mail messages to export", vbOKOnly, "Error"
    Exit Sub
ElseIf FolderSelected.DefaultItemType <> olMailItem Then
     MsgBox "These are not Mail Items", vbOKOnly, "Error"
    Exit Sub
ElseIf FolderSelected.Items.Count = 0 Then
    MsgBox "There are no mail messages to export", vbOKOnly, "Error"
    Exit Sub
End If
  'Copy field items in mail folder.
 
intRowCounter = 1
colidx = 1
wks.Cells(intRowCounter, colidx) = "To": colidx = colidx + 1
 wks.Cells(intRowCounter, colidx) = "From": colidx = colidx + 1
wks.Cells(intRowCounter, colidx) = "Subject": colidx = colidx + 1
wks.Cells(intRowCounter, colidx) = "Body": colidx = colidx + 1
 wks.Cells(intRowCounter, colidx) = "Received": colidx = colidx + 1
wks.Cells(intRowCounter, colidx) = "Folder": colidx = colidx + 1
wks.Cells(intRowCounter, colidx) = "Category": colidx = colidx + 1
 wks.Cells(intRowCounter, colidx) = "Flag Status": colidx = colidx + 1
intRowCounter = wks.UsedRange.Rows.Count
For Each itm In FolderSelected.Items
intColumnCounter = 1
If TypeOf itm Is MailItem Then
 Set msg = itm
intRowCounter = intRowCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.To
varSender = msg.SenderEmailAddress
'============================================================
If InStr(1, msg.SenderEmailAddress, "501288010", vbTextCompare) > 0 Then
     varSender = "Todd Curphey"
Else
    varSender = msg.SenderEmailAddress
End If
If InStr(1, msg.SenderEmailAddress, "CN=RECIPIENTS/CN=", vbTextCompare) > 0 Then
    varSender = "SSO: " & Right(msg.SenderEmailAddress, 9)
 Else
    varSender = msg.SenderEmailAddress
    varSender = msg.SenderName
End If
'============================================================
intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = varSender
 intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.Subject
intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = Left(msg.Body, 50)
intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.ReceivedTime
 intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = FolderSelected.Name
intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.Categories  '  shows names
 intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.FlagStatus   ' 1 is complete , 2 is flagged, 0 is unmarked
intColumnCounter = intColumnCounter + 1: Set rng = wks.Cells(intRowCounter, intColumnCounter): rng.Value = msg.TaskCompletedDate
 End If 'TypeOf
Next itm
DoEvents
Loop
  Set appExcel = Nothing
  Set wkb = Nothing
Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set FolderSelected = Nothing
Set itm = Nothing
  Exit Sub
ErrHandler:  If Err.Number = 1004 Then
MsgBox strSheet & " doesn't exist", vbOKOnly, "Error"
 Else
MsgBox Err.Number & "; Description: " & Err.Description & vbCrLf & msg.ReceivedTime & vbCrLf & msg.Subject, vbOKOnly, "Error"
End If
Set appExcel = Nothing
Set wkb = Nothing
 Set wks = Nothing
Set rng = Nothing
Set msg = Nothing
Set nms = Nothing
Set FolderSelected = Nothing
Set itm = Nothing
End Sub

Re: Switch Last name and first Name

for first name use =TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
for last name use =TRIM(Right(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Re: Switch Last name and first Name

why dont you use the substitute formula? have you tired it?

Dear Gurus,

i have posted the question in the following link, however did not get a solution, grateful if you take a look into my vba code.

am using excel vba with word object library to create a memo from the ranges in excel cells.

now i am typing some texts and when i moved from my first typetext to the second with type paragraph then there is a considerablly big space between first line and the second line. i can remove that via word document manually "Line and Paragraph Spacing" on the dropdown "Remove Space After Paragraph" however i am trying to perform the same manual thing through the VBA code. i searched through Google and i found in some forums that either .Paragraphs.SpaceBefore = 0 or .Paragraphs.SpaceAfter = 0 which work, however i tried and still did not work. i do not know where to put the .Paragraphs.SpaceAfter = 0 either before the second text or after it. any help would be appreciated.

P.S when i use record macro to check how macro is recording the code. unfortunately recording macro does not record any code for "Line and Paragraph Spacing" on the dropdown "Remove Space After Paragraph".

With WordApp .Documents.Add With .Selection.TypeText Text:="Project Advances and Project Expenses".TypeParagraph.TypeText Text:="Project Budget & Project Cash"

link posted in another forum. attached below
http://www.mrexcel.com/forum/e…-spacebefore-%3D-0-a.html

Re: Formula SUMPRODUCT

Payment sent.

Re: Formula SUMPRODUCT

Perfect. time to pay you. pm me your paypal ID

Quote from S M C;651445

OK, here's the code. You need to alter the FormulaA and FormulaB reference to what you need.

Code

Sub PutFormula()


    Dim lng As Long
    
    With Worksheets("Expenditure_Details")
        lng = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("BQ2:BQ" & lng).Formula = "=IF(SUMIFS(DEEMED_ACCRUAL_COL,PO_ID,AZ2,DATA_SLICE,BH2)>0,DEEMED_ACCRUAL_COL,0)"
        On Error Resume Next
        .Parent.Names("DEEMED_ACCRUALS_VALID").Delete
        Err.Clear: On Error GoTo 0
        .Parent.Names.Add "DEEMED_ACCRUALS_VALID", .Range("BQ2:BQ" & lng)
    End With
    Worksheets("Template").Range("FormulaB").FormulaArray = "=MAX(VALUE(FISCAL_YEAR_EXP))"
    Worksheets("Template").Range("FormulaA").Formula = _
        "=SUMPRODUCT(DEEMED_ACCRUALS_VALID,--(VALUE(FISCAL_YEAR_EXP)=FormulaB),--(DATA_SLICE=""A-2""),--(DATA_LAYER=""DEEMED_ACCRUALS""),--ISNUMBER(MATCH(BUDGET_YEAR,{2013,2012},0)))"
    
End Sub

Display More

Re: Formula SUMPRODUCT

yes. i do not need the pivot table. that was just for reference.

i now tried with this code and it gets stuck on the last line

Sub FillinFormula()
FinalCol = Worksheets("Expenditure_Details").Cells(1, Columns.Count).End(xlToLeft).Column
FinalRow = Worksheets("Expenditure_Details").Cells(Rows.Count, 1).End(xlUp).Row
Nexcol = FinalCol + 1
Worksheets("Expenditure_Details").Cells(2, Nexcol).FormulaR1C1 = "=IF(SUMIFS(DEEMED_ACCRUAL_COL,PO_ID,RC[-17],DATA_SLICE,RC[-9])>0,DEEMED_ACCRUAL_COL,0)"
Worksheets("Expenditure_Details").Cells(2, NextCol).AutoFill Destination:=Worksheets("Expenditure_Details").Cells(2, FinalRow)
End Sub

Quote from S M C;651433

Also, from the sample file, I assume you don't need the pivot table (that was just for reference I believe?)

Re: Formula SUMPRODUCT

also PM me your paypal ID, so that i transfer money.

Re: Formula SUMPRODUCT

Thanks SMC.

this is good, but i have lots of files and manual entering the Expenditure_Details!BQ2 the drag down formula will be too much work.

may i ask you to fix my VBA code to prepapulate the formula in the Expenditure_Details!BQ2?

i have written the following code to enter automatically the formula in my column BQ, my vba does not give error but it does not enter the formula there.

Sub FillinFormula()
Dim lastRow As Long
 
lastRow = Sheets("Expenditure_Details").Cells(Rows.Count, "BQ").End(xlUp).Row
For I = lastRow To 1
Sheets("Expenditure_Details").Cells(1, 39).FormulaR1C1 = "IF(SUMIFS(DEEMED_ACCRUAL_COL,PO_ID,RC[-17],DATA_SLICE,RC[-9])>0,DEEMED_ACCRUAL_COL,0)"
Next
End Sub

Quote from S M C;651400

In Expenditure_Details!BQ2, use the formula =IF(SUMIFS(DEEMED_ACCRUAL_COL,PO_ID,AZ2,DATA_SLICE,BH2)>0,DEEMED_ACCRUAL_COL,0) and drag down all the way

Give this column (same as you've done in other columns) as name as DEEMED_ACCRUALS_VALID

For formula A, use

=SUMPRODUCT(DEEMED_ACCRUALS_VALID,--(VALUE(FISCAL_YEAR_EXP)=2013),--(DATA_SLICE="A-2"),--(DATA_LAYER="DEEMED_ACCRUALS"),--ISNUMBER(MATCH(BUDGET_YEAR,{2013,2012},0)))

For formula B, use an array formula (enter the formula using CTRL+SHIFT+ENTER together)

in place of the bold 2013 in Formula A, refer to the value retured by formula B

Display More

Re: Formula SUMPRODUCT

Quote from S M C;651303

Doable I would say. I can work with your file tomorrow. Late night here. If that's late for you, other developers can look at this.

thanks SMC

Take your time. i will wait for another 12 hours.

Re: Formula SUMPRODUCT

Thanks SMC. please let me know, if you have any question.

Quote from S M C;651290

I will look at it now

Gurus,

10% already deposited to "[email protected]" Transaction ID 2DJ43190E01573448
can anyone look into this file uploaded here https://skydrive.live.com/view…esid=D7C00A2BF29043E0!245[/email]

"[email protected]"

"[email protected]"

"[email protected]"

"[email protected]"

"[email protected]"

"[email protected]"