Posts by Maqbool

Thank you very much. Appreciated.

Hi Carim:

Thank you very much for the help. Really appreciated.

Sorry:

Doesn't work as expected.

I have added another transaction in the last row. Expected average is 442.01 instead the formula suggested is giving me 400.40

Please see the attachment.

Best Regards,

Maqbool

Thank you verymuch for the reply and appreciated.

The Average Purchase Price needs to be calculated based on the entire database considering the sale, not on a monthly basis.

This February position sold out on Oct. 25th.

Then initated another purchase (100 qty) on 17th Dec. If there is no purchase after that your above formula will work fine. But another purchase booked on 23 Dec. (25 qty). Out this only 75 shares sold on 28/12. Until the entire position is sold out, need to get the average using the purchase value of Dec. 23 and Dec. 25 so on.....

I have updated the spreasheet with expected result in column I.

If not clear let me know will try to explain it further.

Best Regards

Maqbool

Hi Everyone:

Hope everyone is doing well and safe.

I need an help to find out the average purchase price of a stock. When i sell the entire position and initiate a new position for the same stock the average calculation should not include the purchases initated prior to "sale of entire position". I have attached a sample spreadsheet. On the average price column, i have calculated the average price using SUMIFS, unfortunately it wont gives me the correct average price if there are multiple purchase and sales. I have created a column for the expected result.

I am using Office 365 and any solution using formula or query is fine.

Best Regards

Maqbool

Hi

I think the problem is here:

Quote from JimmyB

With ActiveCell
'>>>>>>>>>>> Failing to paste NEXT LINE <<<<<<<<<<<<
ActiveSheet.Paste

End With

Can you try replacing it with

with ActiveSheet
 .activecell.paste

end with

Regards

Maqbool

Hi

Can you please post a sample workbook with expected results?

Hi Bob:

Please read the forum rules. You need to tag the code when you post.

I am not sure i understand you fully.

Try the following:

Dim strDate As String

'paste below code after "IF sheets("Mandatory").Cells("i,1)...................................... Then"

With Sheets("Mandatory") 
strDate = Me.Textbox4.Value
If IsNumber(strDate) Then  'check the value is in textbox 
.Cells(i, 5).Value = strDate 
Else 
MsgBox "Invalid Date."
 End If
End With

otherwise post a sample workbook with expected result.

Maqbool

Please note that: this is to plot chart. Otherwise I can use a conditional formatting to hide.

Hi Everyone:

Hope Everyone doing well.

Can someone help me on how to hide the running total of a field on a Pivot Table. See the attached spreadsheet. I need to hide the highlighted numbers until the months data available on table. Any help will be appreciated.

Regards

Maqbool

Hi

Welcome to the forum:

Do you want store all changes (I mean, history of the changes) every changes on the column (what changed to what and the time of both changes) etc..
Can you please provide a sample of this?

Regards,

Maqbool

Hi

Seems the search result is not in ActiveSheet. You have to Activate the sheet where the search result is:

Sheets("Search Trial").activate
activesheet.range(schoice).select

Regards

Maqbool

Thank you Ali.

Finally, I managed to do this. Thanks a lot for the help.

Regards,

Maqbool

Thank you for the reply.

No - I need a flexibility to choose historic files also. I found a way to do this with latest file . That does not meet my requirement

Maqbool

Hi
Can someone guide me how to dynamically change the source of a Power Query; My source files are in a folder. I am trying to change the source of the query by typing the file name in a cell. I found few videos which takes all files from the folder. But I need to use a single file as my data source. This source files are created on a weekly basis with a date suffix.
Thank you very much
Any help will be highly appreciated.

Hi

Try the code below:

Sub SumSheets()


Sheets("Summary").Range("A2:A1000").ClearContents  ' clear everything in summary before pasting


Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Summary" And sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then
        
            sh.Range("F17:F66").Copy
            Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
        End If
    Next


End Sub

Regards

Maqbool

Hi

Code below will solve both issues:

Please leave blank row 1 on consolidation workbook.

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim strLkpArra As String




Set wbSls = ThisWorkbook
strPath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!R7C2:R"


With wbSls
    Set wsSls = .Sheets("Sales")
End With


n = 11


With wsSls
    Do Until n = lstRw
        lstRwInClsd = .Range("D4").Value + 6
        x = .Range("D8").End(xlToRight).Column
        lstRw = .Rows.Count
        lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
   
        For x = 4 To x
            strfile = .Cells(8, x)
            strLkpArray = strPath & strfile & "]" & strSht
            'levae row 1 empty on consolidated workbook - this helper row to get the last row in closed workbook
            .Cells(1, x).Value = "=COUNTA('" & strPath & strfile & "]Master Data'!$B$8:$B$10000)"
            lstRwInClsd = .Cells(1, x).Value + 6 '(6 rows blank on the monthly workbook - may need to adjust accordingly)
            .Cells(n, x).Value = "=VLOOKUP(RC2," & "'" & strLkpArray & lstRwInClsd & "C11,10,FALSE)"
            .Cells(1, x).Value = ""
        Next
        n = n + 1
    Loop
End With


End Sub

Regards

Maqbool

Hi

Change the code as below:

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String
Dim lstRw As Long
Dim rngFmula As Range


Set wbSls = ThisWorkbook
strpath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"


With wbSls
    
    Set wsSls = .Sheets("Sales")
  
End With
n = 11


With wsSls
    Do Until n = lstRw
        x = .Range("D8").End(xlToRight).Column
        lstRw = .Rows.Count
        lstRw = .Range("D" & lstRw).End(xlUp).Offset(0, -1).Row
   
        For x = 4 To x
            strfile = .Cells(8, x)
            strlkparray = strpath & strfile & "]" & strSht
           .Cells(n, x).Value = "=VLOOKUP($B11," & strlkparray
            
        Next
        n = n + 1
    Loop
End With


End Sub

Regards

Maqbool

Hi

Try the following. I have noticed an issues with your file extension (why you do have January 2010.xlsm.xlsx) . This needs to be corrected. Or change the file names on row 8.

Sub Test()
Dim wbSls As Workbook
Dim wbMnthly As String
Dim wsSls As Worksheet
Dim x As Long
Dim strDir As String


Set wbSls = ThisWorkbook
strpath = "'C:\Test\["  ' change accordingly
strSht = "Master Data'!$B$7:$K$33,10,FALSE)"


With wbSls
    Set wsSls = .Sheets("Sales")
End With


With wsSls
    x = .Range("D8").End(xlToRight).Column
    
    For x = 4 To x
        strfile = .Cells(8, x)
        strlkparray = strpath & strfile & "]" & strSht
        Debug.Print strlkparray
        .Cells(11, x).Value = "=VLOOKUP($B11," & strlkparray
    Next
End With


End Sub

Regards

Maqbool

Hi

I can't upload the workbook now. I will upload the workbook later today.

Follow the below steps:

Create three ranged names as below:

Use below formula against each name

[F]
tblData: Sheet1!$C$1:INDEX(Sheet1!$C$1:$G$1000,COUNTA(Sheet1!$C:$C),5)

rngCat : =INDEX(tblData,0,2)

rngPercent =OFFSET(Sheet1!$C$1,MATCH(Sheet1!$J$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!$J$1))
[/F]

Then create the following formula on your sample sheet
L21 - enter as an array formula (CTRL+SHIFT+Enter]

[F]

N21 = LARGE(rngPercent,$K21)

L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)

M21= VLOOKUP(L21,tblData,MATCH($M$20,$C$1:$G$1,0),FALSE)

[/F]

Regards

Maqbool

Hi

try the below :

Sub Test()
Dim wsOne As Worksheet
Dim rngOne As Range
Dim testOne As String
Dim testTwo As String


Set wsOne = ThisWorkbook.Sheets("Sheet1")


With wsOne
    x = .Rows.Count
    Set rngOne = .Range("F2", .Range("F" & x).End(xlUp))
End With


With rngOne
    n = rngOne.Count
    For x = n To 1 Step -1
        testOne = .Cells(x, 1).Value
        testTwo = .Cells(x, -1).Value
        Debug.Print testOne
        If testOne = "" And testTwo = "AFC" Then
            .Cells(x, -1).Font.Bold = True  'this will highlight the cell which is going to be deleted
            '.Cells(x, -1).EntireRow.Delete 'to delete entire row - enable this part, if everything works as expected
        End If
    Next
End With




End Sub

Regards

Maqbool