Please help me to find this answer

[Manoj Kumar Gupta]

Dear VBA Guru

Please le me know the solution of the following.

1.How to put excel formula in vba

   suppose =sum(a2:a100)

2.How to read any Excel Range using VBA

3.How to open multi workbook and copy a1 range from all workbook

4.What is the Acronyms of vba

 

 

 

 

Please reply.Thanks in advance 

[Vasant]

Hi Manoj,

 

Pls find the answers below.

1.

Activesheet.range("A1").formula="=sum(a2:a100)"

 

2.

Sub ReadRange()
' selected range
Set Rng = ActiveSheet.Range("A1:D10")
' loops thru each cell in the
For Each cls In Rng
' Prints the value in the cells
Debug.Print cls.Value
Next
End Sub

 

3.

' Loop through all open workbooks in a particular excel application instance

    For Each wbk In Workbooks
        Debug.Print wbk.Name
    Next

4. Visual Basic for Applications

 

 

Regards

Vasant

--
You received this message because you are subscribed to the Google Groups "MS Excel Macro Vba" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to excel_vba+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/excel_vba?hl=en.

--
Regards

Vasant

[angel angel]

Hello Manoj,
I think this will help you.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
'''''''''''''''''''''''''''''''''''''''''''''excel formula in vba
Sub PutFormula()
'''''Putting Excel formula =SUM(A2:A50) IN D2 RANGE
ActiveWorkbook.Sheets("SHEET1").Range("D2").Select
Selection.FormulaR1C1 = "=SUM(RC[-3]:R[48]C[-3])"
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''read any Excel Range using VBA
Sub ReadXlsRange()
'''''''Read c:c Range Till null
For counter = 1 To 65535
        Set curcell = Worksheets("Sheet1").Cells(counter, 3)
        MsgBox curcell
        If curcell.Value = "" Then counter = 65535
    Next counter
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''Open Workbook and Copy range
Sub OpenFileAndCopyRange()
    Dim Workbk1 As Workbook
    Dim WrkDstn As Workbook
    Dim i As Long
         ''FOR CURRENT WORKBOOK WHERE WE WILL PUT COPIED RANGE
     Set WrkDstn = ThisWorkbook
          'Open up your first workbook, copy data
    Set Workbk1 = Workbooks.Open("D:\SOLVSAMP.XLS")
    'Application _
    '.GetOpenFilename ("xls files (*.xls), *.xls")
    For i = Workbk1.Sheets.Count To 1 Step -1
        Workbk1.Sheets(i).Range("B5:B400").Copy
        WrkDstn.Sheets("Sheet1").Cells(2, 1).Insert shift:=xlShiftToRight
        WrkDstn.Sheets("Sheet1").Cells(2, 1).PasteSpecial xlValues
    Next
    'To display no alerts
   Application.DisplayAlerts = False
    'Close first workbook
    Workbk1.Close savechanges:=False
       
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

4.Visual Basic for Application

 

 

 

On Wed, Jun 1, 2011 at 10:55 AM, Manoj Kumar Gupta <sendtomano...@gmail.com> wrote:

--

[rf1234 rf1234]

Hello Manoj

Try to copy specific range using Application.GetOpenFilename().

Try to follow the guidelines from Vasant and Angel.

 

 

'''''''''''''''''''''''''''''''''''''''''''''''*******************************''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit
Sub OpenNcopyOneFile()
''''''''OPEN AND COPY RANGE FOR ONE FILE USING GetOpenFilename
Dim FName As Variant
Dim wrkbooks1 As Workbook
Dim wrkbooks2 As Workbook
Set wrkbooks1 = ThisWorkbook
Application.DisplayAlerts = False
Application.DisplayAlerts = False
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Application.ScreenUpdating = False
Set wrkbooks2 = Workbooks.Open(FName)
wrkbooks2.Sheets("Sheet1").Range("A1:C1").Copy wrkbooks1.Sheets("Sheet1").Range("A1")
wrkbooks2.Close False
Application.ScreenUpdating = True
End If
End Sub

 

'''''''''''''''''''''''''''''''''''''''''''''''*******************************''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

--
Regards,
Prashant Tripathi
Engineer-SW
Mobile: 0017202597567
Please consider the environment before printing.
----------------------------------------------------
Immer zielen auf die vollkommene Harmonie des
 Denkens & Wort & deed.Always zielen darauf ab,
 reinigen Sie Ihre Meinung und alles wird gut.
 ----------------------------------------------------
Always aim at complete harmony of thought &
 word & deed.Always aim at purifying your
thoughts & everything will be well.