View Full Version : Problems writing data to worksheet
dennygibson
03-29-2019, 03:40 PM
I need to write, from a vba code, to a worksheet. I haven’t been able to figure out how to write the data in the format I need. The data is in a 2d array. Let’s say there are four variable values I want to print. Here’s the format I need:
X_index, Y_index, variable 1, variable 2, variable 3, variable 4 ... Y needs to be constant while writing the variables in the same row as x changes ... until Y changes, the next row is filled with the 6 values above. Hope this makes sense ... help anyone?
大灰狼1976
03-31-2019, 07:40 PM
This requirement should be simple, but you need to send an attachment.
-Okami
dennygibson
04-01-2019, 09:26 AM
This is what I want:
jz ir r z Awest Aeast Asouth Anorth
0 0 value value value value value value
0 1 value value value value value value
0 2 value value value value value value
0 3 value value value value value value
0 4 value value value value value value
0 5 value value value value value value
0 6 value value value value value value
1 0 value value value value value value
1 1 value value value value value value
1 2 value value value value value value
1 3 value value value value value value
1 4 value value value value value value
1 5 value value value value value value
1 6 value value value value value value
With this code:
jrow = Range("k32").Row
icol = Range("k32").Column
' jz = 0
Dim ir1 As Integer
For jz = 0 To 1
For ir = 0 To nr
nbrIndex ir, jz, nr, n, s_index, n_index, w_index, e_index
ActiveSheet.Cells(jrow + jz, icol).Value = jz
ActiveSheet.Cells(jrow + jz, icol + 1).Value = ir
ActiveSheet.Cells(jrow + jz, icol + 2).Value = r(ir, jz)
ActiveSheet.Cells(jrow + jz, icol + 3).Value = z(jz)
ActiveSheet.Cells(jrow + jz, icol + 4).Value = aface_west(n)
ActiveSheet.Cells(jrow + jz, icol + 5).Value = aface_east(n)
ActiveSheet.Cells(jrow + jz, icol + 6).Value = aface_south(n)
ActiveSheet.Cells(jrow + jz, icol + 7).Value = aface_north(n)
Next ir
Next jz
This is what I get:
jz ir r z Awest Aeast Asouth Anorth
0 6 7.8520E-04 0.0000E+00 8.5275E-05 8.5571E-05 5.5811E-05 4.87046E-05
1 6 6.9993E-04 8.5275E-05 3.5354E-05 4.8705E-05 3.53543E-05
Notice that only the last values of ir are respresented so it keeps writing over the same range.
With this code:
jrow = Range("k32").Row
icol = Range("k32").Column
' jz = 0
Dim ir1 As Integer
For jz = 0 To 1
For ir = 0 To nr
nbrIndex ir, jz, nr, n, s_index, n_index, w_index, e_index
ActiveSheet.Cells(jrow + ir, icol).Value = jz
ActiveSheet.Cells(jrow + ir, icol + 1).Value = ir
ActiveSheet.Cells(jrow + ir, icol + 2).Value = r(ir, jz)
ActiveSheet.Cells(jrow + ir, icol + 3).Value = z(jz)
ActiveSheet.Cells(jrow + ir, icol + 4).Value = aface_west(n)
ActiveSheet.Cells(jrow + ir, icol + 5).Value = aface_east(n)
ActiveSheet.Cells(jrow + ir, icol + 6).Value = aface_south(n)
ActiveSheet.Cells(jrow + ir, icol + 7).Value = aface_north(n)
Next ir
Next jz
This is the result ;
1 0 7.2832E-05 8.5275E-05 8.5275E-05 8.5571E-05 5.5811E-05 4.87046E-05
1 1 1.7735E-04 8.5275E-05 3.5354E-05 8.6104E-05 1.1162E-04 6.77273E-05
1 2 2.8186E-04 8.5275E-05 3.5354E-05 4.8705E-05 3.53543E-05
In this case jz is overwritten ... I need to display all of the values of jz (and their associated data) along with all the values of ir
the function called in the previous sections (thoroughly checked returns the adjacent neighbor cell locations)
Function nbrIndex(ir, jz, nr, n, s_index, n_index, w_index, e_index)
Dim ip1, im1, jp1, jm1 As Integer
ip1 = ir + 1
im1 = ir - 1
jp1 = jz + 1
jm1 = jz - 1
n = ir + jz * nr
s_index = ir + jm1 * nr
n_index = ir + jp1 * nr
w_index = im1 + jp1 * nr
e_index = ip1 + jm1 * nr
End Function
There has got to be a way to display this data the way I want … God, do I miss Fortran!
Paul_Hossler
04-01-2019, 10:34 AM
That's not really an attachment
Create a workbook with the macros and sample data, and a CLEAR example of the result
That way anyone inclined to help won't have to spend time re-creating test data
My sig has directions to attach a file
I can tell by your variable names and style that you are used to FORTRAN, but this IS Excel and in (mostly) object-oriented so I think you'll find that it's workwhile to accept the new paradigm
dennygibson
04-01-2019, 10:48 AM
Paul … I'm only asking for someone to tell me how to write this data … it doesn't require you looking at my 800 line code. You've been really helpful in the past … but this is just fussy … I'll upload what you're asking for but you're going to wish I hadn't. This isn't someone's recipe for cookies … it's a full blown dynamic fluid analysis. Actually … I don't have time for this … I understand your rules but I also know when you're being ridiculous … forget it!
dennygibson
04-01-2019, 10:51 AM
I'll send you the whole file ... you could just answer my questin or you can spend hours farretting this thing out ...
dennygibson
04-01-2019, 11:03 AM
Here's the whole deal ... good luck. I have been dead-in-the-water for four days … If I can't see the data computed I can't judge whether it's correct or not … sorry for the temper-tantrum but this is extremely frustrating. Paul … object oriented or not writing some calculated data to a spreadsheet should not be difficult!
Paul_Hossler
04-01-2019, 11:26 AM
writing some calculated data to a spreadsheet should not be difficult!
It's not
Here's two ways. The first is procedural and the second is more Excel-like
Option Explicit
Sub test()
Dim A(1 To 3, 1 To 4) As Double
Dim r As Long, c As Long
Dim r1 As Long, c1 As Long
'fill array
A(1, 1) = 11
A(1, 2) = 12
A(1, 3) = 13
A(1, 4) = 14
A(2, 1) = 21
A(2, 2) = 22
A(2, 3) = 23
A(2, 4) = 24
A(3, 1) = 31
A(3, 2) = 32
A(3, 3) = 33
A(3, 4) = 34
'start cell row/col
r1 = 32
c1 = 11
'put data in
For r = LBound(A, 1) To UBound(A, 1)
For c = LBound(A, 2) To UBound(A, 2)
ActiveSheet.Cells(r1 - 1 + r, c1 - 1 + c).Value = A(r, c)
Next c
Next r
End Sub
Sub test2()
Dim A(1 To 3, 1 To 4) As Double
Dim r1 As Long, c1 As Long
'fill array
A(1, 1) = 11
A(1, 2) = 12
A(1, 3) = 13
A(1, 4) = 14
A(2, 1) = 21
A(2, 2) = 22
A(2, 3) = 23
A(2, 4) = 24
A(3, 1) = 31
A(3, 2) = 32
A(3, 3) = 33
A(3, 4) = 34
'start cell row/col
r1 = 32
c1 = 11
'put data in
ActiveSheet.Cells(r1, c1).Resize(UBound(A, 1), UBound(A, 2)).Value = A
End Sub
It's really only necessary to attach a workbook with enough data and macros that demonstrates just the issue
大灰狼1976
04-01-2019, 10:38 PM
jrow = Range("k32").Row
icol = Range("k32").Column
' jz = 0
Dim ir1 As Integer
For jz = 0 To 1
For ir = 0 To nr
nbrIndex ir, jz, nr, n, s_index, n_index, w_index, e_index
ActiveSheet.Cells(jrow + ir, icol).Value = jz
ActiveSheet.Cells(jrow + ir, icol + 1).Value = ir
ActiveSheet.Cells(jrow + ir, icol + 2).Value = r(ir, jz)
ActiveSheet.Cells(jrow + ir, icol + 3).Value = z(jz)
ActiveSheet.Cells(jrow + ir, icol + 4).Value = aface_west(n)
ActiveSheet.Cells(jrow + ir, icol + 5).Value = aface_east(n)
ActiveSheet.Cells(jrow + ir, icol + 6).Value = aface_south(n)
ActiveSheet.Cells(jrow + ir, icol + 7).Value = aface_north(n)
Next ir
jrow = jrow + nr + 1 'Or jrow = jrow + ir
Next jz
dennygibson
04-01-2019, 10:53 PM
jrow = Range("k32").Row
icol = Range("k32").Column
' jz = 0
Dim ir1 As Integer
For jz = 0 To 1
For ir = 0 To nr
nbrIndex ir, jz, nr, n, s_index, n_index, w_index, e_index
ActiveSheet.Cells(jrow + ir, icol).Value = jz
ActiveSheet.Cells(jrow + ir, icol + 1).Value = ir
ActiveSheet.Cells(jrow + ir, icol + 2).Value = r(ir, jz)
ActiveSheet.Cells(jrow + ir, icol + 3).Value = z(jz)
ActiveSheet.Cells(jrow + ir, icol + 4).Value = aface_west(n)
ActiveSheet.Cells(jrow + ir, icol + 5).Value = aface_east(n)
ActiveSheet.Cells(jrow + ir, icol + 6).Value = aface_south(n)
ActiveSheet.Cells(jrow + ir, icol + 7).Value = aface_north(n)
Next ir
jrow = jrow + nr + 1 'Or jrow = jrow + ir
Next jz
dennygibson
04-01-2019, 10:55 PM
jrow = Range("k32").Row
icol = Range("k32").Column
' jz = 0
Dim ir1 As Integer
For jz = 0 To 1
For ir = 0 To nr
nbrIndex ir, jz, nr, n, s_index, n_index, w_index, e_index
ActiveSheet.Cells(jrow + ir, icol).Value = jz
ActiveSheet.Cells(jrow + ir, icol + 1).Value = ir
ActiveSheet.Cells(jrow + ir, icol + 2).Value = r(ir, jz)
ActiveSheet.Cells(jrow + ir, icol + 3).Value = z(jz)
ActiveSheet.Cells(jrow + ir, icol + 4).Value = aface_west(n)
ActiveSheet.Cells(jrow + ir, icol + 5).Value = aface_east(n)
ActiveSheet.Cells(jrow + ir, icol + 6).Value = aface_south(n)
ActiveSheet.Cells(jrow + ir, icol + 7).Value = aface_north(n)
Next ir
jrow = jrow + nr + 1 'Or jrow = jrow + ir
Next jz
dennygibson
04-01-2019, 10:56 PM
Thank you sir!
大灰狼1976
04-01-2019, 11:00 PM
That's OK:yes
Aussiebear
04-04-2019, 01:45 AM
Paul … I'm only asking for someone to tell me how to write this data … it doesn't require you looking at my 800 line code. You've been really helpful in the past … but this is just fussy … I'll upload what you're asking for but you're going to wish I hadn't. This isn't someone's recipe for cookies … it's a full blown dynamic fluid analysis. Actually … I don't have time for this … I understand your rules but I also know when you're being ridiculous … forget it!
For someone who is asking for assistance from our members, this type of attitude will hardly endear you or your request to others. Please be more respectful to others.
Paul_Hossler
04-04-2019, 06:44 AM
I took this as frustration boiling over and a too fast a click on the [Submit] button
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.