Macro Help ???

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
Hi,

Try this macro:
Code:
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

Board Regular
Joined
Jan 17, 2005
Messages
101
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
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

Board Regular
Joined
Jan 17, 2005
Messages
101
This is the part of the code that it gets hung up on.

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

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
Hi,

You could just try the code as posted,in which case you would be prompted for the file, or use this (untested) version:
Code:
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
 

Forum statistics

Threads
1,141,681
Messages
5,707,796
Members
421,529
Latest member
Balintn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top