PDA

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