Posts by shahji

No problem but thanks for trying :).

Thanks Abhi,

Yes I am using excel VBA to write the code which is to be used on a spreadsheet.

I have tried a couple of things but the macro still would not insert formulae depending on the myRange number.

if i type in the Inputbox 1 or 2 it is fine but when i type in 3 it will still fill two columns and when the input box value is 4 it will insert formulae in 3 columns and so on. Dont know how to fix that problem.

Also as i asked before that how can i use the Inputbox value in the actual formula.

Thanks abhi,

I have been working on the code and i have come up with the following:

Sub AddData()

Dim i As Integer, j As Integer


rowcounter = Intersect(ActiveSheet.UsedRange, Columns("B:D")).Rows.Count
    
      myRange = Application.InputBox(prompt:="Beneficiaries", Type:=1)
               
       For i = 18 To rowcounter
        For j = 5 To myRange
         
         If Range("B" & i) = "" And Range("C" & i) = "" Then
                Range("E" & i) = ""
        
                Else
                Sheets("Investments").Cells(i, j).Value = "=ROUNDDOWN((R34C4/R1C5)/R34C4*RC2,0)"
                j = j + 1
                Sheets("Investments").Cells(i, j).Value = "=ROUNDDOWN(RC[-1]*RC3,0)"
                
                
         End If
        Next j
 
       Next i
       
End Sub

The code runs fine when i put value 1,2,3 in the input box but then it does not work afterwards. I need the code to run as many times as i say in the Inputbox, i.e. it could 1 time 2 or 10 times. It should put the formulae into the next empty box.

Is it also possible that the formulae i am using, i could also put the Inputbox value like below?

"=ROUNDDOWN((R34C4/InputBox)/R34C4*RC2,0)"

Many thanks,

Shah

Hello all,

I am trying to write a code. I need the code to do the following:

An input box asks for a number,
Then the below code divides values in a given column and paste them in the next available empty column. Then the 2nd formula runs and it places the values in the next available column.

Now this routine should repeat as many time as the number in the input box.

I have written/recorded the following but dont know how to add the input box which should do as i explained above.

Sub Analysis()

  
    rowcounter = Intersect(ActiveSheet.UsedRange, Columns("B:D")).Rows.Count
      
   For i = 18 To rowcounter
        
        Range("E" & i).FormulaArray = "=ROUNDDOWN((R34C4/2)/R34C4*RC[-3],0)"
        Range("F" & i).FormulaArray = "=ROUNDDOWN(RC[-1]*RC[-3],0)"
        If Range("B" & i) = "" And Range("C" & i) = "" Then
        Range("E" & i) = ""
        
       
        End If

    Next i
    

End Sub

Please let me know if you need any more information.

Thanks for help,

Shah.

Thanks Salem,

Range("B3:B2000").Value = Range("B3:B2000").Value

The Code puts the formula in each cell and formula gets the value from other sheet, at the end it changes all the cells to values rather than having the formula behind the scenes.

This is just one column and it takes about 40 seconds to complete this and I will be running this on 80 columns on two sheets. It is taking about 30mins on one sheet. Was wondering if there is another way to get the data.

Dear all,

I have the following macro and have tried a few things to make it run quicker but it still takes too long.
the below code only runs on one column but i have 40 such columns. so all in all macro takes about 30 mins to run.
Any idea how i can make it faster or other ways of achieving the same result.

Sub Sumproduct()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

Dim LastRow As Long


With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B3").Resize(LastRow - 1).Formula = "=SUMPRODUCT((S6OBData!A:A=A3)*(S6OBData!B:B=B$2),S6OBData!C:C)"

Range("B3:B2000").Value = Range("B3:B2000").Value

End With

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

many thanks for help,

Shah

Hello everyone,

I am posting a Vlookup Macro. thanks to Brian Baulsom. I hope this will help everyone. If you have any questions please let me know.

'=========================================================
'- GENERIC LOOKUP MACRO TO
'- FIND A VALUE IN ANOTHER WORKSHEET
'- AND RETURN A VALUE FROM ANOTHER COLUMN
'=========================================================
'- select the cell containing the first search value
'- and run this macro from there.
'- can be set to continue down the column
'- [** need to make changes below as required **]
'- Brian Baulsom May 2005
'==========================================================
Dim MyValue As Variant
Dim FromSheet As Worksheet
Dim LookupColumn As Integer
Dim FromRow As Long
Dim FromColumn As Integer
'-
Dim ToSheet As Worksheet
Dim StartRow As Long
Dim LastRow As Long
Dim ActiveColumn As Integer
Dim ReturnColumnNumber
Dim ToRow As Long
Dim FoundCell As Object

'=============================================================
'- MAIN ROUTINE
'=============================================================
Sub DO_LOOKUP()
Application.Calculation = xlCalculationManual
'----------------------------------------------------------
'- LOOKUP SHEET [**AMEND AS REQUIRED**]
Set FromSheet = Sheets("S6values")
LookupColumn = 1 ' look for match here
FromColumn = 2 ' return value from here
'-----------------------------------------------------------
'- ACTIVE SHEET
Set ToSheet = Sheets("MonthlyFigures")
ActiveColumn = 1
StartRow = 6
'-------------------------------------------------------------
'- COMMENT OUT UNWANTED LINE, UNCOMMENT THE OTHER
'- ..............................[** FOR MULTIPLE ROWS **]
'LastRow …

Hello,

I am trying to write a macro to add a few headings. I have the following macro.

Sub DOPaste()

Dim i As Integer

y =11
z = y

Do While Sheets("Investments").Range("A" & y).Value <> ""

i = 1

Sheets("Investments").Cells(z + 1, "D") = "Interim for year"
Sheets("Investments").Cells(z + 3, "D") = "2nd lnterim for year"
Sheets("Investments").Cells(z + 6, "D") = "3rd lnterim for year"
Sheets("Investments").Cells(z + 9, "D") = "Final for year"

i = i + 1
Loop

End Sub

Basically what i want the macro to do is paste the above four headings if there is a value in column A. Lets say Column A has a value in row 11 then the macro will past the headings in the column D at the specified rows.
let suppose if column A has two or three values in Row 11, 25, 39 and so on then the macro should do the same procedure and add the headings in column D starting from row 11 and then 25 and then 39.

my macro do it for once and stops there. i want it to carryon and past the same headings at intervals given above if there is a value in Column A.

thanks for help,

Shah

thanks man,

we are nearly there. the macro works for one client but it does not pick the balances for remaining clients. lets say 0014 is the first client and 0027A is the 2nd client and 0027A2 is the third client.
the macro stops at first client (0014) and pastes the value.
i want it to keep looking in sheet2 column A and get the balances from sheet1 column C until there are no more clients in Sheet2 column A.

thanks

hey Cguan,

thanks for getting back to me.

after a long research i have found the following macro which copies data based on a condition and runs a loop as well.

Sub CopyData()
Dim lRow As Long, x As Long, y As Long, z As Long

lRow = Sheets("sheet1").range("A65535").End(xlUp).Row

y = 2

For x = 2 To lRow
If Sheets("sheet1").range("B" & x) = "700" Then
Sheets("sheet2").range("B" & y).Value = Sheets("sheet1").range("C" & x).Value

y = y + 1
End If
Next
End Sub

one problem remains.
i would like the macro to past value in front of the corresponding client. as from my previous example client "0014" has a "700" code. this macro picks up all the balances which are "700" but it does not match them with the right "client". any idea how i can do that. as both sheets have client codes in Column A.

thank you very much.

shah

hey Cguan,

thank you for the reply. the macro works but copies the entire row to sheet2.

nearly there but not quite. two more things
1) if i need to look for a whole column instead of y2 = "0014" for example y2 = A:A in sheet 2
2)I need to copy the value in Sheet1 Column C only not the entire row.

thanks

hey Cguan,

i have tried the above but it copies the macro to sheet2 rather than any data.
any ideas why is that?

thanks

thank for getting back to me,

well i have attached a small example.

sheet1 has three columns "Client", "Code" and "Balance" and sheet2 has two columns "Client"and "Balance".

i want to copy data from Sheet1 "Balance" to Sheet2 "Balance". the data should correspond to the "client" column.
there are many entries in "Code" but i am just interested in a few. lets say "Code" is "700" and i want to copy "balance" for Client "0014" and past it in sheet2 "Balance" corresponding to the client.

i hope this will help,

btw i was able to loop the macro but as it is not dynamic it just keeps on going through the sheets and copies nothing.

thank you for your help.

thank you cguan,

the code works fine.
i was wondering if i could run a loop on this as the code stops after looking at the first value in sheet1 and copies it to sheet2.

Also is it possible to give it more than one codition lets say it looks in sheet2 column1 for a value and copy a value from sheet1 column 3 corresponding to the value in sheet2 column1 and a condition in sheet1 column2.

i hope i have explained it ok.

thank you

well thank you for your reply,

I would not have to calculate anything as all the values are already in the database. i would need to find a specific result and copy over to sheet2. i hope this helps,

thanks,

Shah

Hello,

i am working on a macro where i need to copy data from a sheet to a different sheet in the same workbook. i normally use "vlookup" or "Sumif" or "Sumproduct" formulae to do the job. the datas is quite large and it takes ages to refresh the data when i use "Sumif" or "Vlookup". i am looking for a macro which could replace these formulae. I have included a short example of the database.

Sheet1 has the data and it should be pasted on Sheet2 by looking in Sheet1 column "Code" should be used as a condition. for every "700" there are a few value and they should be taken from Sheet1 and pasted in Sheet2 corresponding to the "Client". same with other "Codes" and i might need to add the Balances in sum instances.

thank you in advance for your help,

Shah

thank you very much Tu22,

i am very new to VBA and it seems very difficult for me to understand the code you have provided me with. i have attached an example of my database which is a cut down version.

On the Datasheet i have columns B, C, D and E which are named and same names are used on other sheets as well. I need to copy the data in from the Datasheet and past is in relevant sheets which correspond to the Trust No..

there is one problem though that there are more than one row which will correspond to the same sheet. i do not want to over write the information so the macro should offset/ go to blank row each time before pasting the information.

once again thank you for your help.

shah

I am trying to create a macro which could copy information from a datasheet and past it into sheets which are named as one of the Cells in column A.

I have a datasheet which contains five columns full of data. Column A contains the Client references. There are 200 sheets and each client reference has a seperate sheet which has datafields.

I need a mcro which could lookup client reference in datasheet column A and find the sheet named with the client reference then copy information from datasheet row B:E and paste it in column A:E of reference sheet.

thank you for your help,

shah