How can I put the results of the Array back to the corresponding cells?

[idnoidno]

Sub FillBlank()
Dim marr, rg
Dim i, j As Integer


Worksheets("1").Select
Set rg = Range("a1:t" & Cells(Rows.Count, 1).End(xlUp).Row)
marr = rg
For j = 11 To 20
For i = 6 To UBound(marr)
If marr(i, j) = "" Then
Cells(i, j) = Cells(3, j)
End If
Next i
Next j
End Sub

What's the difference If Cells(i, j) = Cells(3, j) is replaced by marr(i,j)=marr(3,j)?

[mdmackillop]

3 points:
Give your posts meaningful titles
Use code tags
Provide a sample file on which your code can be run

[Bob Phillips]

It would mean that the values are not updated on the worksheet, Cells is a range, marr is an array.

[idnoidno]

Very sorry, my expression is not very correct, I want to know is that the way with the Cell I can understand, but the use of Array, how can I put the results of the Array back to the corresponding cells?

[idnoidno]

3 points:
Give your posts meaningful titles
Use code tags
Provide a sample file on which your code can be run
Sorry, I can not find how to edit the title of the post.

The excel file is here.


19082

[mancubus]

http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_posthelp_faq_item

[Bob Phillips]

Sub FillBlank()
Dim marr As Variant, rg As Range
Dim i As Long, j As Long


With Worksheets("1")

Set rg = .Range("K6:T" & .Cells(.Rows.Count, 1).End(xlUp).Row)
marr = rg

For j = 1 To 10

For i = 1 To UBound(marr, 1)

If marr(i, j) = "" Then marr(i, j) = .Cells(3, j + 10)
Next i
Next j

rg = marr
End With
End Sub

[idnoidno]

Thank you very much for the editor to help me to edit the subject.

[mancubus]

:thumb

[idnoidno]

Sub FillBlank()
Dim marr As Variant, rg As Range
Dim i As Long, j As Long


With Worksheets("1")

Set rg = .Range("K6:T" & .Cells(.Rows.Count, 1).End(xlUp).Row)
marr = rg

For j = 1 To 10

For i = 1 To UBound(marr, 1)

If marr(i, j) = "" Then marr(i, j) = .Cells(3, j + 10)
Next i
Next j

rg = marr
End With
End Sub


If further, each row, we calculate the sum of each row ,like this
I6*I2+J6*J2+……S6*S2=T6
I7*I2+J7*J2+……S7*S2=T7
I8*I2+J8*J2+……S8*S2=T8

T6+T7+T8………………..=T57
and so on

I would like to ask how to integrate in your program?

[idnoidno]

Option ExplicitSub taa()
Dim i, j As Integer
Dim marr, mbrr
Dim rg As Object
Dim tsum As Long


With Worksheets("sheet1")
Set rg = .Range("j1:t" & Cells(Rows.Count, 1).End(xlUp).Row)
marr = rg
Range("t:t").ClearContents


For j = 1 To 10
For i = 6 To UBound(marr, 1)
If marr(i, j) = "" Then
marr(i, j) = .Cells(3, j + 9) * 0.9
End If
marr(i, 11) = marr(i, 11) + marr(i, j) * Cells(2, j + 9)
Next i
Next j


rg = marr


End With


End Sub




This is my way, but when i execute it once again, the sum of the numbers has been increased again, how can i edit it to solve this problem?

[mdmackillop]

Range("t:t").ClearContents
marr = rg

[idnoidno]

Range("t:t").ClearContents
marr = rg



Thank you very much for your answer, but can you explain it? I'm not sure what's the difference between these.

[mdmackillop]

Order of events.

[idnoidno]

Order of events.
So simple text, I still do not understand what you mean

[mdmackillop]

Have you tested the revised code? Does it work differently?

[idnoidno]

It works.

[rlv]

Is there a reason you are using arrays for this?


Sub taa_2()
Dim i, j As Integer
Dim rg As Range
Dim rngFactors As Range
Dim tsum As Double

With Worksheets("sheet1")
Set rg = .Range("j6:s" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rngFactors = .Range("J2:S2")

With rg
For i = 1 To .Rows.Count
tsum = 0
For j = 1 To .Columns.Count
tsum = tsum + .Cells(i, j) * rngFactors.Cells(1, j)
Next j
.Cells(i, .Columns.Count + 1) = tsum
Next i
End With
End With
End Sub

[idnoidno]

Will the same EXCEL file, if there are other conditions, how can I edit my code?



Sub bbb()
Dim i, j As Integer
Dim mar, bar
Dim rg As Range


With Worksheets("sheet1")
Range("t:t").ClearContents
bar = .Range("j3:s3")
Set rg = .Range("j1:t" & Cells(Rows.Count, 1).End(xlUp).Row)
For j = 1 To 10
mar = rg
For i = 6 To UBound(mar)
If mar(i, j) = "" Then
mar(i, j) = .Cells(3, j + 9) * 0.9
End If
If mar(i, j) > bar(1, j) * 0.8 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9)
ElseIf mar(i, j) <= bar(1, j) * 0.8 And mar(i, j) > bar(1, j) * 0.6 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) + 0.8
ElseIf mar(i, j) <= bar(1, j) * 0.6 And mar(i, j) > bar(1, j) * 0.4 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) * 0.6
ElseIf mar(i, j) <= bar(1, j) * 0.4 And mar(i, j) > bar(1, j) * 0.3 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) * 0.4
ElseIf mar(i, j) <= bar(1, j) * 0.3 And mar(i, j) > bar(1, j) * 0.15 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) * 0.15
End If
Next i
Next j

End With


rg = mar
End Sub

[mdmackillop]

I don't know what your intentions are but you are looping through all columns and returning results only from the final column
See attached

[idnoidno]

I don't know what your intentions are but you are looping through all columns and returning results only from the final column
See attached
Thanks a lot.
I think I don't express it clearly, I will study your code this night.

[idnoidno]

Can I ask you? What is the logical thinking of writing code?
This is the biggest problem I am now experiencing.

[Bob Phillips]

Not sure I understand the question. You write code because it cannot be done any other way, it is more efficient with code, or you want to control what happens. What problem are you referring to?

[idnoidno]

I may not be clear enough to say that I mean it's hard for me to understand how someone else wrote the code to solve the problem. I have to work on an attachment file to solve the problem, It is in calculating how much the cost per person to pay, Each cost is to use j6: s56 range, multiplied by j2: s2, plus total respectively，However, the j6: s56 range has 5 discount levels,With the five discount levels listed on the left side of the table.

[idnoidno]

I don't know what your intentions are but you are looping through all columns and returning results only from the final column
See attached

You provide the file is very close to what I mean,
but I would like to ask, how to edit the code, so that result of sum(U: AD) for each ROW appear in the "T" column.

[mdmackillop]

Reverse the loops and include a summing variable
Option Explicit



Sub bbb()
Dim i, j As Integer
Dim mar, bar
Dim rg As Range
Dim x
Dim Rslt As Single

With Worksheets("sheet1")
Range("t:t").ClearContents
bar = .Range("j3:s3")
Set rg = .Range("j1:t" & Cells(Rows.Count, 1).End(xlUp).Row)
mar = rg

'For j = 1 To 10
'Cells(5, "t").Offset(, j) = j
'Next j

For i = 6 To UBound(mar)
Rslt = 0
For j = 1 To 10

If mar(i, j) = "" Then
mar(i, j) = .Cells(3, j + 9) * 0.9
End If
If mar(i, j) > bar(1, j) * 0.8 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9)
'Cells(i, "t").Offset(, j) = mar(i, 11)
Rslt = Rslt + mar(i, 11)
ElseIf mar(i, j) <= bar(1, j) * 0.8 And mar(i, j) > bar(1, j) * 0.6 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) + 0.8
'Cells(i, "t").Offset(, j) = mar(i, 11)
Rslt = Rslt + mar(i, 11)
ElseIf mar(i, j) <= bar(1, j) * 0.6 And mar(i, j) > bar(1, j) * 0.4 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) * 0.6
'Cells(i, "t").Offset(, j) = mar(i, 11)
Rslt = Rslt + mar(i, 11)
ElseIf mar(i, j) <= bar(1, j) * 0.4 And mar(i, j) > bar(1, j) * 0.3 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) * 0.4
'Cells(i, "t").Offset(, j) = mar(i, 11)
Rslt = Rslt + mar(i, 11)
ElseIf mar(i, j) <= bar(1, j) * 0.3 And mar(i, j) > bar(1, j) * 0.15 Then
mar(i, 11) = mar(i, 11) + mar(i, j) * Cells(2, j + 9) * 0.15
'Cells(i, "t").Offset(, j) = mar(i, 11)
Rslt = Rslt + mar(i, 11)
End If
Next j
Cells(i, "t") = Rslt
Next i

End With
Cells(5, "t") = "Result"


End Sub

[idnoidno]

Wow, I did not expect to change the loop count variable.

[idnoidno]

Someone can answer me, why in the loop calculation, will use the temporary variables in code?

[mdmackillop]

You were asked this in by RLV in post 18 and you persevered with the array in post 19. Your code was edited as requested, not rewritten to remove the array method.

[idnoidno]

Dear dude,
the code "temporary variable=temporary variable+ operation method " is typical code syntax?
I wish you could understand the way I expressed it.