Posts by rameshjeni

rameshjeni · Dec 20th 2020

I had made an Excel based FIFO app for use in my Stock market trades. While this template works OK sometimes it seems to ignore some "sell" trades and result is in error. Also sometimes it gives some blank areas in the "Results" sheet.

How it works: you enter the trade transactions in "Transaction" tab as per structure given and run "FIFOData_v_10May15" macro. it will create buy and sell trades in the FIFO tab based on FIFO logic. You can then use it make yr P and L estimate. It handles both short and long (normal) sales.

Problem is as follows

-some areas are left blank and have to be manually filled.

-Some transactions are ignored and left out of reconciliation. Not sure when this happens.

-difficult to modify if my requirement changes

-unitising the cost as the addon s like unit/total brokerage are shown wrong and I have to adjust manually in the extended area.

The reliance file esp has some vanishing transactions issue.

Can someone suggest how to improve or rectify? (Though I am not very conversant with VBA I have struggled thru help from experts in this forum and Google and got this working from as basic system given by an expert in this group (his name is Shark)).

Pl let me know if you need any clarifications

rameshjeni · May 9th 2012

I tried a simple array sub routine to pass an array from a sheet (a1:a10) and receive this array into another sub routine. The idea is to do an elaborate array multiplication. It doesn't seem to work. Not sure where am I going wrong as this is a standard code taken from MSDN site

Code

Sub Pass_array()
            Dim multiplicant1 As Variant
            multiplicant1 = Range ("a1:a10").Value
            receive_array multiplicant1
         End Sub


         Sub receive_array(thisarray) 'is it here?
            For i = 1 To UBound(multiplicant1)
            	MsgBox multiplicant1(i, 1)
            Next
         End Sub

Display More

any suggestions?

rameshjeni · May 7th 2012

Re: Variable from Combobox not being accessed in my own sort subroutine

Can someone help me on this asap???

rameshjeni · May 4th 2012

I made a VBA code that first reads the worksheet names into a combo box. Then I can select a worksheet and perform a SORT operation based on 2 criteria.
The variable YY is declared as public in the form module so that it can be accessed in the SortThisWB module/ . On running the code It gives subscript out of range error. Any ideas on what is the issue here

Code

Option Explicit
Dim N As Long
Public YY As String


Sub GetWorksheetNames()
'show the Form "SheetList"        
SheetList.Show
End Sub




Private Sub ComboBox1_Click()
YY = Me.ComboBox1.Value
Msgbox ("HELLO-You have selected Sheet "" " & YY & " "" --Are you sure????")
Worksheets(YY).Select
Unload Me
End Sub




Private Sub UserForm_Activate()
'Loads all worksheet names to a combobox
For N = 1 To ActiveWorkbook.Sheets.Count
    ComboBox1.AddItem ActiveWorkbook.Sheets(N).Name
    Next N
End Sub




Sub SortThisWB()
GetWorksheetNames


   Cells.Select
    ThisWorkbook.Worksheets(YY).Sort.SortFields.Clear
    ThisWorkbook.Worksheets(YY).Sort.SortFields.Add Key:=Range("A2:A22") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ThisWorkbook.Worksheets(YY).Sort.SortFields.Add Key:=Range("E2:E22") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ThisWorkbook.Worksheets(YY).Sort
        .SetRange Range("A1:N22")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
ActiveWorkbook.Save
    
End Sub

Display More

rameshjeni · Apr 24th 2012

I have a problem in passing a value selected to input box to a variable inside a formula as given below

Code

Sub Macrotest()
'
' Macrotest Macro


Dim offset1 As Long
offset1 = InputBox (prompt:="Please Select offset", Title:="offset setter", Default:="4")
     
    Range("R10").Select
    ActiveCell.FormulaR1C1 = "=LEFT(INDIRECT(""D"" & offset1),5)". 


End Sub

Display More

rameshjeni · Apr 14th 2012

Re: Pastespecial does not work in VBA code

Thank Rory, it works now.

rameshjeni · Apr 14th 2012

I am attaching a code where I get the details of folder and file names and based on that do a series of actions on two files. The code gets stuck in the step "Workbooks(MyFilename).Activate". Basically the file name "MyFilename" from Open_File function is not getting passed to the " OpenWorkbookFromFolder" sub.

Code

Sub OpenWorkbookFromFolder()
'To open files and / or folders using the FollowHyperlink method
     'Open Folder
    Dim strFolder As String
    Dim strXLSFile As String
    Dim strXLSFilepath As String
    'Open excel workbook=source
    Open_File
    Msgbox "My File is" & "  " & MyFilename
    'Open excel workbook activate workbook and worksheet to copy from
    Workbooks(MyFilename).Activate
    Sheets("Sheet1").Activate
    Range("A1:M3").Select
    Selection.Copy
    
    'Open excel workbook -destination
    Open_File
    Workbooks(MyFilename).Activate
    Sheets("Sheet1").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Rows("4:11").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub


Function Open_File() As String()
    Dim sFile As String
    Dim MyFilename As String
    Dim MyFolder As String
    MyFolder = InputBox("folder name", "enter folder name", "C:\")
   
   'this is just to check the input box selects correctly
    Msgbox "My Folder is" & "  " & MyFolder 
    
   MyFilename = InputBox("Enter file number", "enter file name", "Test") & ".xls"
  
   'this is just to check the input box selects correctly
   Msgbox "My File  is" & "  " & MyFilename
    sFile = MyFolder & MyFilename
    
    ActiveWorkbook.FollowHyperlink Address:=sFile, NewWindow:=True
       
    'Workbook.Open Filename:=sFile
End Function

Display More

any suggestions on where the problem could be?

rameshjeni · Apr 13th 2012

Re: Pastespecial does not work in VBA code

Thanks for the quick response. While running it I get the error "Compile error :object required". Am I missing something?

rameshjeni · Apr 12th 2012

I have attached the code where I am trying to select a user range thru a subroutine and use this generically to copy from one sheet and paste it to another sheet I am using pastespecial as I want to copy only the format and not the values. The code dos not work and gets stuck at highlighted line in the attachment.the message i get is Run time error 1004 . pasteSpecial method of range class failed Any suggestions to correct this error?

Code

Option Explicit
Public UserRange As Range
 
Sub SelUserRange()
'Selects Range based on user inpout
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Delete Blank Rows ", Type:=8)
Application.Goto UserRange
'Msgbox("UserRange is" & UserRange, vbOKOnly, "JRM", 8) As VbMsgBoxResult
End Sub
 
Sub RangeCopyPaste()
    Sheets("Sheet1").Select
    SelUserRange
    Application.CutCopyMode = False
    Selection.Copy
   
    Sheets("COGNI").Activate
    SelUserRange
    UserRange.Activate
    UserRange.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    Exit Sub
 
Canceled:
   
End Sub

Display More

The crux of the problem

First sub selects a range to be copied in Sjheet1 as specified by the user
Second subroutine activates a sheet “COGNI”: and tried to paste into a location specified by user thru input box
The macro is stuck at highlighted point

Posts by rameshjeni

FIFO calculation - error in my app-help needed

array function in VBA has issues

Variable from Combobox not being accessed in my own sort subroutine

Variable from Combobox not being accessed in my own sort subroutine

inputbox value not paased to variable

PasteSpecial does not work in VBA code

Problem in accessing file name from Input box

PasteSpecial does not work in VBA code

PasteSpecial does not work in VBA code