Posts by HowardC

I have two categories of Subscriptions

Category A - $ 3950 per Annum
Category B $ 3150 per annum

We would like both categories to be the same after 6 years and would like a formula to calculate what interest rate needs to be charged so that both categories pay the same after 6 years

It would be appreciated if someone could please assist me

I have a workbook with a sheet called "sick & Training"

The training days is indicated by a "t' and sick days with an "s"

I have another sheet called "Training & Sick Leave taken" where I have set up formulas to compute the sick & training days taken, but the formula returns zero

I would like someone to please assist me in amending the formula to I can compute the number of training (t) days and sick (s) days

I have attached a sample workbook

Re: Copy data after where "totaL" appears to last row above

Hi Jindon

Thanks very much for your help. Code works perfectly

Re: Copy data after where "totaL" appears to last row above

Thanks for the amended code

When running the macro, the data is not copied to above where total appears

See attached sample data. I have manually copied the data and shown in yellow what it must look like after being copied

Once copied the data below total must be cleared

Kindly heck & amend code accordingly

Re: Copy data after where "totaL" appears to last row above

Hi Jindon

Thanks for the help.

However the code does not copy the data below Total to the row below the last row of data above total

Re: Copy data after where "totaL" appears to last row above

Thanks very much for your help. Code works perfectly

Re: Copy data after where "totaL" appears to last row above

Thanks for the reply. Your comments have been noted

I will incorporate a macro into your code to insert 20 rows above total

The number of rows below total will be 10 at the most

Once the data have been copied, the data below total must be cleared

When I am trying to do is as follows:

1) The data in Col A:B below Total (Col A) to be copied to Cols A:B after the last row of data above total
2) The data in Col E below Total (Col A) to be copied to Col C after the last row of data above total
3) The data in Col H below Total (Col A) to be copied to Col D after the last row of data above total
4) The data in Col D below Total (Col A) to be copied to Col E after the last row of data above total
5) The data in Col F:G below Total (Col A) to be copied to Cols F:G after the last row of data above total

Re: Copy data after where "totaL" appears to last row above

Thanks for all your help so far

I have tested the further and where I have more than one row of data below total, the data is not copied correctly

I have attached sample data

Kindly test and amend code

Re: Copy data after where "totaL" appears to last row above

Thanks for the explanation

Re: Copy data after where "totaL" appears to last row above

Thanks for all you help, much appreciated

Please explain your code below

y(1) = x(1, 1): y(2) = x(1, 2): y(3) = x(1, 5) 
    y(4) = x(1, 8): y(5) = x(1, 4): y(6) = "": y(7) = x(1, 7)

Re: Copy data after where "totaL" appears to last row above

Quote from mdorey;747126

upload some example

Thanks for the reply. I have attached sample data. I have manually copied the data below total to the last row containing data above total. I have highlighted the data copied in yellow, which I would like to be automated using VBA

It would be appreciated if you would kindly assist me

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

I have text 'total" in Column A

I need a macro to copy data the appears 4 rows below "Total" to the cell below the last row of data before total as follows:

1) The data in Col A:B below Total (Col A) to be copied to Cols A:B after the last row of data above total
2) The Total in Col D below Total (Col A) to be copied to Col E after the last row of data
3) The Total in Col F below Total (Col A) to be copied to Col F after the last row of data (if any)
4) The Total in Col G below Total (Col A) to be copied to Col G after the last row of data (if any)

5) The Total in Col I below Total (Col A) to be copied to Col D after the last row of data

I need someone on this forum to please help me resolve this

I know how to write code to copy data, but this one is tricky

I have have some basic code to copy from Col A:C , but need this amended so as to copy the data below total to the relevant columns above total

Sub_Copy_Data_Below_Total ()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:C" & lr).Copy Destination:=Range("A") ?
End Sub

I have also posted on Mr Excel.com

http://www.mrexcel.com/forum/e…7409-copy-data-total.html

Please could someone kindly assist me

Re: Macro to remove values in formulas

Hi Alan

All the adjustments will begin with a plus or a minus sign

From time to time I need to amend formula by adding or subtracting a value when making adustments

At the beginning of each year, I need to manually remove all the values the appear directly at the end of the formula on all sheets except the first and last sheet

for examples -see below

=+F208/2-2000 -I need to manually remove -2000)

IF(G191="Left",0,IF(G197="",0,Commission9(G197+G195)))-2500 -I need to remove - 2500

Your assistance in resolving this is most appreciated

I have also posted on MrExcel.com

http://www.mrexcel.com/forum/e…values-after-formula.html

Re: Run Time error when running Application.run

Hi Guys

Thanks for your input. I have finally resolved my problem

Option Explicit[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12] [/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]Sub Open_All_Workbooks()[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]    Application.ScreenUpdating= False[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]       Dim c As Range[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]       Dim fpath As String[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]       [/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]        fpath= "C:\Parts & SVC Sales\"[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]       [/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]        ForEach c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]           Workbooks.Open (fpath & c.Value & ".xlsm")[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]           Application.Run "'" & ActiveWorkbook.Name &"'!Auto_Update"[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]           ActiveWorkbook.Save[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]           ActiveWorkbook.Close[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]           CloseCSV[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]       Next c[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12] [/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12]   Application.ScreenUpdating = True[/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][FONT=Calibri][size=12] [/SIZE][/FONT][FONT=Times New Roman][size=12][/SIZE][/FONT][size=12][FONT=Calibri]End Sub

[/FONT][/SIZE]

Re: Run Time error when running Application.run

Thanks for pointing this out

Will assign Fpath a value and will test this evening & advise if I need any further help

Re: Run Time error when running Application.run

Thanks for pointing this out

I have picked up where I have gone wrong-I never declared variables as folows:

Will test this evening and let you know

Dim FPath as string
Dim fName As String

Re: Run Time error when running Application.run

Application.run is to run the macro on the workbooks in "C:\Parts & SVC Sales

I get a run time error in the destination workbook when running application.run. "Errror 1004 "Cannot run the macro "!auto_Update!' The macro may not be available in this workbook or all macros may be disabled

The following code is highlighted

Application.Run "'" & fName & "'!Auto_Update"

My full code is

Sub Open_All_Workbooks()
    Application.ScreenUpdating = False
    Dim c As Range
   
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
   
        Workbooks.Open ("C:\Parts & SVC Sales\" & c.Value & ".xlsm")
  Next c
     fName = Mid(fPath, Len("C:\Parts & SVC Sales") + 2)
    Application.Run "'" & fName & "'!Auto_Update"
    
      ActiveWorkbook.Save
      ActiveWorkbook.Close
      CloseCSV
   
    Application.ScreenUpdating = True
End Sub

I have several workbooks in folder C:\Parts &Service where the data needs to be updated with downloaded files in C:\extract

I have a macro that allows me to open up a workbook in c:\Parts & SVC Sales. One opened I then need to select the appropriate file in C:\extract

Sub Update_Workbooks()
   ChDir ("C:\Parts & SVC Sales")
    Application.DisplayAlerts = False
    fPath = Application.GetOpenFilename
    fName = Mid(fPath, Len("C:\Parts & SVC Sales") + 2)
    Workbooks.Open (fPath)
    Application.Run "'" & fName & "'!Auto_Update"
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        CloseCSV
End Sub

The are two types of workbooks in C:\Parts & SVC Sales "Parts Sales" and "service Sales" . The branch name is at the beginning of the file name

Instead of opening up each file individually in C:\Parts & SVC Sales and selecting the appropriate csv file in C:/extract using the Update_Macro, I would like the macro to open up all the files in the directory C:\Parts & SVC Sales and update each of these with the appropriate csv file in C:\extract

The name and description type must match the csv files and then updated eg Br1 Parts Sales to be updated with csv file Br1 Salesperson 01-07-2014 (the date in the file is not important for match the parts file -the branch name for eg BR1 and "salesperson is) i.e Br1 Parts Sales must select BR1 Salesperson 01-07-2014

BR1 Service Sales Must be updated with Br1 Service order repair register.csv (branch name for eg Br1 in this instance name and Service order repair register (pertain to Service Sales) is important)

Br2 Parts Sales to be updated with Br2 Salesperson
Br2 Service Sales must be updated with Br2 Service order repair register etc

I have also posted on Mr Excel.com
http://www.mrexcel.com/forum/e…5-updating-workbooks.html

Your assistance in resolving this is most appreciated