Import data, calculate, export data
 

Intermediate Excel user. No Access or Visual Basic experienc
whatsoever.

Here's what I need to do. I have an Excel spreadsheet of over 10,000
rows of data in workbook 1. I have another worksheet 2 with a bunch o
input fields and formulas.

How do I setup a Macro so that the selected fields in Worksheet 1 ar
imported into the input fields in Worksheet 2 and then automaticall
copies the results for each calculation back into either Worksheet 1 o
a new Worksheet 3?

Thanks

--
Message posted from http://www.ExcelForum.com

Import data, calculate, export data
 

Since you don't know VBA, then is sounds like you want someone to program
this for you.

If so, then vague descriptions about copy to a sheet, do calcs, copy back
can not be implemented in code.

Be specific

In sheet1 (Sheet name is DATA), data in columns A to M, with data starting
in A2, Headers in row 1, for each row, copy the values from Sheet2 (sheet
name CALC)

column B goes to Sheet2 Cell A9
column C goes to Sheet2 Cell F21
column D goes to Sheet2 Cell E12
etc

force sheet2 to calculate (if necessary)

Return the value in sheet2, Z21 to the same row, column N,
Return the value in sheet2, R13 to the same row, Column O

the more specific you are, the less you will have to fix on your own.

--
Regards,
Tom Ogilvy


"darkstar218 " wrote in message
...
Intermediate Excel user. No Access or Visual Basic experience
whatsoever.

Here's what I need to do. I have an Excel spreadsheet of over 10,000+
rows of data in workbook 1. I have another worksheet 2 with a bunch of
input fields and formulas.

How do I setup a Macro so that the selected fields in Worksheet 1 are
imported into the input fields in Worksheet 2 and then automatically
copies the results for each calculation back into either Worksheet 1 or
a new Worksheet 3?

Thanks.


---
Message posted from http://www.ExcelForum.com/

Import data, calculate, export data
 

Wow! That was quick.

Sorry for the vague entry. I'll try to be more specific.

---

In sheet1 (Sheet name is Combined DATA) with data starting
in A2, Headers in row 1, for each row, copy the data values from colum
N into cell E5 on sheet2(sheet name Monthly).

column Q (combined Data) into cell E7 (Monthly)
column O (combined Data) into cell E6 (Monthly)

Force Monthly to calculate

Return the value in 'Monthly' cell J5 to 'Combined Data' cell HP in th
same row.

Return the value in 'Monthly' cell J6 to 'Combined Data' cell HQ in th
same row.

Return the value in 'Monthly' cell J7 to 'Combined Data' cell HR in th
same row.

Return the value in 'Monthly' cell J8 to 'Combined Data' cell HS in th
same row.

Return the value in 'Monthly' cell J9 to 'Combined Data' cell HT in th
same row.


Repeat until no more rows of data in 'Combined Data'

-----

THANK YOU, THANK YOU, THANK YOU

--
Message posted from http://www.ExcelForum.com

Import data, calculate, export data
 

Test the macro on a copy of your workbook to make sure it does what you
want.

Sub CalcData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng As Range
Dim cell As Range
Set sh1 = Worksheets("Combined DATA")
Set sh2 = Worksheets("Monthly")

Set rng = sh1.Range(sh1.Cells(2, 1), _
sh1.Cells(2, 1).End(xlDown))
For Each cell In rng
sh2.Range("E7").Value = _
sh1.Cells(cell.Row, "Q").Value
sh2.Range("E6").Value = _
sh1.Cells(cell.Row, "O").Value
sh2.Calculate
sh1.Cells(cell.Row, "HP").Value = _
sh2.Range("J5").Value
sh1.Cells(cell.Row, "HQ").Value = _
sh2.Range("J6").Value
sh1.Cells(cell.Row, "HR").Value = _
sh2.Range("J7").Value
sh1.Cells(cell.Row, "HS").Value = _
sh2.Range("J8").Value
sh1.Cells(cell.Row, "HT").Value = _
sh2.Range("J9").Value
Next
End Sub

go to the VBE (alt+F11). Select insert Module

Paste in the above code.

Go back to Excel

go to Tools=Macro=Macros, select CalcData and click Run

--
Regards,
Tom Ogilvy


darkstar218 wrote in message
...
Wow! That was quick.

Sorry for the vague entry. I'll try to be more specific.

---

In sheet1 (Sheet name is Combined DATA) with data starting
in A2, Headers in row 1, for each row, copy the data values from column
N into cell E5 on sheet2(sheet name Monthly).

column Q (combined Data) into cell E7 (Monthly)
column O (combined Data) into cell E6 (Monthly)

Force Monthly to calculate

Return the value in 'Monthly' cell J5 to 'Combined Data' cell HP in the
same row.

Return the value in 'Monthly' cell J6 to 'Combined Data' cell HQ in the
same row.

Return the value in 'Monthly' cell J7 to 'Combined Data' cell HR in the
same row.

Return the value in 'Monthly' cell J8 to 'Combined Data' cell HS in the
same row.

Return the value in 'Monthly' cell J9 to 'Combined Data' cell HT in the
same row.


Repeat until no more rows of data in 'Combined Data'

-----

THANK YOU, THANK YOU, THANK YOU!


---
Message posted from http://www.ExcelForum.com/

Import data, calculate, export data
 

::THANK YOU! THANK YOU! THANK YOU!

IT WORKED PERFECTLY!:

--
Message posted from http://www.ExcelForum.com