Macro Help ???

[JOSTERBAUER]

How do I start to write this macro. I want to accomplish this. I want to take the numbers in H93:H140 on sheet 1, and add it into another woorkbook (Variance report) in a different location. I want that bank of number to grab the same amount of cells in Variance report workbook, and add them to it.

ex there is qty of 2 in cell h:93 on sheet 1 and it will grab the first cell in the bank on Variance report that has a qty of 3, and add the qty of 2 to say it know has a qty of (5)

I need help on getting this written.

 

[al_b_cnu]

Hi,

Try this macro:

Sub AddEmUp()
Const cFromRange As String = "H93:H140"
Const cVarRepFirstCell As String = "A1"
Dim iColOff As Integer
Dim rRowOff As Long
Dim R As Range
Dim vFileToOpen As Variant, vValue As Variant
Dim WSFr As Worksheet, wsTo As Worksheet

vFileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If vFileToOpen = False Then
    MsgBox "Macro abandoned"
    Exit Sub
End If

Set WSFr = Sheets("Sheet1")
iColOff = Range(cVarRepFirstCell).Column - Range(cFromRange).Column
rRowOff = Range(cVarRepFirstCell).Row - Range(cFromRange).Row

Workbooks.Open Filename:=vFileToOpen
Set wsTo = ActiveWorkbook.Sheets("Sheet1")

For Each R In WSFr.Range(cFromRange)
    vValue = Val(wsTo.Range(R.Address).Offset(rRowOff, iColOff).Value) + _
             Val(R.Value)
    wsTo.Range(R.Address).Offset(rRowOff, iColOff).Value = vValue
Next R

With Application
    .DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    .DisplayAlerts = True
End With
End Sub

 

[JOSTERBAUER]

Sorry I'm kind of blank when it comes to some things. Here is how I wrote it, tell me what is wrong?


Private Sub Variance_Click()
Const cFromRange As String = "H93:H140"
Const cVarRepFirstCell As String = "A1"
Dim iColOff As Integer
Dim rRowOff As Long
Dim R As Range
Dim vFileToOpen As Variant, vValue As Variant
Dim WSFr As Worksheet, wsTo As Worksheet

vFileToOpen = Application.GetOpenFilename("F:\ESTIMATES\variance reporting.xls")
If vFileToOpen = False Then
MsgBox "Macro abandoned"
Exit Sub
End If

Set WSFr = Sheets("Bid Sheet")
iColOff = Range(cVarRepFirstCell).Column - Range(cFromRange).Column
rRowOff = Range(cVarRepFirstCell).Row - Range(cFromRange).Row

Workbooks.Open Filename:=vFileToOpen
Set wsTo = ActiveWorkbook.Sheets("Bid Sheet")

For Each R In WSFr.Range(cFromRange)
vValue = Val(wsTo.Range(R.Address).Offset(rRowOff, iColOff).Value) + _
Val(R.Value)
wsTo.Range(R.Address).Offset(rRowOff, iColOff).Value = vValue
Next R

With Application
.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
.DisplayAlerts = True
End With
End Sub

 

[al_b_cnu]

Hi,

open your workbook, type [Alt-F11] then Insert / Module.

Copy the above code & paste into the module code window.

To use, use [Alt-F8], select the macro 'AddEmUp' (r whatever) & 'Run'.

 

[JOSTERBAUER]

This is the part of the code that it gets hung up on.

vFileToOpen = Application.GetOpenFilename("F:\ESTIMATES\variance reporting.xls")

 

[al_b_cnu]

Hi,

You could just try the code as posted,in which case you would be prompted for the file, or use this (untested) version:

Sub AddEmUp()
Const cFromRange As String = "H93:H140"
Const cVarRepFirstCell As String = "A1"
Dim iColOff As Integer
Dim rRowOff As Long
Dim R As Range
Dim vValue As Variant
Dim WSFr As Worksheet, wsTo As Worksheet

Set WSFr = Sheets("Sheet1")
iColOff = Range(cVarRepFirstCell).Column - Range(cFromRange).Column
rRowOff = Range(cVarRepFirstCell).Row - Range(cFromRange).Row

Workbooks.Open Filename:="F:\ESTIMATES\variance reporting.xls"
Set wsTo = ActiveWorkbook.Sheets("Sheet1")

For Each R In WSFr.Range(cFromRange)
    vValue = Val(wsTo.Range(R.Address).Offset(rRowOff, iColOff).Value) + _
             Val(R.Value)
    wsTo.Range(R.Address).Offset(rRowOff, iColOff).Value = vValue
Next R

With Application
    .DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    .DisplayAlerts = True
End With
End Sub