Excel to VB

[Greedo]

I posted this before and I got some help but I think I wasn't really clear enough about the problem.

I am trying to get about 6 cells worth of data from one sheet in excel, about 4 cells worth from another sheet, do some simple calculations with those numbers, and then put the results into a third excel sheet.

Using my current algorithm I need to get all this data into an array multiple times (ie, a 6 x 6 matrix with the same 6 values in different patterns or a 4 x 4 of the same type).

I know how many variables there are, it is always the same: 6 of one type and 4 of the other.

The spreadsheet will already be opened so there shouldn't be any need to open it inside VB (although this might be one of the many things I don't understand).

Anyway, thank you everyone for your patience. I really appreciate it.

 

[Foada]

I am not sure I follow your question, but if you add a command button to the first sheet and add this code to the click event it will read the data from cells at sheet1 top left 6x6, sheet2 top left 4x4 into two arrays, then join the data into the top left 4x4 cells on sheet3. Left me know if this is what you are looking for.

Option Explicit

Private Sub CommandButton1_Click()
Dim arrSh1(6, 6) As String
Dim arrSh2(4, 4) As String
Dim iR As Long
Dim iC As Long

For iR = 1 To 6 'Read the Data From Sheet1 into an array
For iC = 1 To 6
arrSh1(iR - 1, iC - 1) = Sheet1.Cells(iR, iC).Value
Next
Next

For iR = 1 To 4 'Read the Data From Sheet2 into an array
For iC = 1 To 4
arrSh2(iR - 1, iC - 1) = Sheet2.Cells(iR, iC).Value
Next
Next

For iR = 1 To 4 'Merge the Array Data from the first 2 sheet into the 3rd sheet
For iC = 1 To 4
Sheet3.Cells(iR, iC).Value = arrSh1(iR - 1, iC - 1) & ", " & arrSh2(iR - 1, iC - 1)
Next
Next

End Sub If you choose to battle wits with the witless be prepared to lose.

 

[Greedo]

That is pretty close to what I want to do. The cells aren't all nicely arranged though. One is in B20 the other is B18 etc. So, if I understand this (which is possible I suppose) what I want to do is use pretty much the lines you have but add some more of them. So it would look like:

For iR = 1 To 6
For iC = 1 To 6
arrSh1(iR - 1, iC - 1) = Sheet1.Cells(2, 20).Value
arrSh1(iR, iC) = Sheet1.Cells(2,18)Value
etc.
Next
Next

But, my sheets name is Spec(Data) so does that anything?

Also, these aren't strings but rather numbers and they will be calculated around and whatnot but the code should end up looking something like:

For iR = 1 To 4
For iC = 1 To 4
Sheet3.Cells(2, 4).Value = newnumber1
Sheet3.Cells(1, 4).Value = newnumber2
Next
Next

Or whatever. Do I understand what you've said more or less? Thank you for your help.

 

[Foada]

I guess I understand a little better. If you have the cells spread out all over the place you may be better to just assign them to their own variables, something like this

Dim sh1cellB20 As Long 'Assign what ever variable type
Dim sh1cellB18 As Long

Private Sub CommandButton1_Click()
sh1cellB18 = Sheet1.Cells(18, 2).Value
sh1cellB20 = Sheet1.Cells(20, 2).Value
End Sub


As far as the sheet name goes you will need to use the name of the sheet from the code window and not the label on the bottom of the worksheet itself. For example if you change the name the tab on the bottom of sheet1 from sheet1 to Spec(Data) then hit "Alt-F11" you will see the code pane show up and the sheet will be listed the project window as Sheet1(Spec(Data)). The name "Sheet1" in the name you will need to use to reference the cells value in the statement "Sheet1.Cells(2,18).Value. Hope this clears things up a little. If you choose to battle wits with the witless be prepared to lose.

 

[Greedo]

Thanks, yeah that worked. In the process it has, however, become clear that there is another problem.

Again, foada, I have spent about 4 days trying everything under the sun to make that work. You have no idea how grateful I am.