CODEWITHSUNDEEP
excelvbafor-loop
Xavi
Xavi

Reputation: 207

macro looping from each cell from the basis of 2 ranges

I try to create a macro with 2 input box, via the first input box, the user selects a range where it is written on each single cell C or D (Credit or Debit) Via the second input, the user selects a range where it is written on each single cell the corresponding amount.

What I what to do is the following:
if the corresponding cell in the range of Credit/Debit is worth "D", the corresponding cell (I mean the amount corresponding to this Debit) remains postive, otherwise it changes to negative.

For example, If the user selects a range of C/D in column J and then the user selects via the 2nd input box all the corresponding amount in column B, I would like that if J1="D" to return a positive value in B1, otherwise to return a negative value in B1 and so on for all the other rows ...

I try to execute my macro but it does not work properly I get error message #NAME?...

Please find below 2 screenshot (before and after executing the macro) as well the VBA code

If someone could help me, it would be super.

Sub ReturncorrectsignofamountaccordingtoDorC()    
    Dim c As Range
    Dim WorkRng2 As Range
    Dim WorkRng As Range

    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Please select the range D/C", xTitleId, WorkRng.Address, Type:=8)
    Set WorkRng2 = Application.Selection
    Set WorkRng2 = Application.InputBox("Please select the range of amounts", xTitleId, WorkRng.Address, Type:=8)

    For Each c In WorkRng2
        c.FormulaR1C1 = _
          "=IF(c.WorkRng.value=""D"",c.WorkRng2.value= c.WorkRng2.value, c.WorkRng2.value= -c.WorkRng2.value)"
    Next c
End Sub

enter image description here enter image description here

Upvotes: 0

Views: 48

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

The issue is that you are mixing vba inside the string. You need to pull out the vba and concatenate:

 Range("A1").Formula = "=SUM(" & Range("A2").Address & ")"

The references c.WorkRng.value are also incorrect. c is a range object and not the parent of WorkRng

also the formula you are trying to insert will be circular and cause a lot of issues, just multiply the value by 1 or -1

Sub ReturncorrectsignofamountaccordingtoDorC()

    Dim c As Range
    Dim WorkRng2 As Range
    Dim WorkRng As Range

    Set WorkRng = Application.InputBox("Please select the range D/C", Type:=8)
    Set WorkRng2 = Application.InputBox("Please select the range of amounts", Type:=8)



    For Each c In WorkRng2
        c.Value = c.Value * IIf(c.Offset(0, WorkRng.Column - c.Column).Value = "D", 1, -1)
    Next c
End Sub

Personally I would use variant arrays to speed things up:

Sub ReturncorrectsignofamountaccordingtoDorC()


    Do
        Dim WorkRng As Range
        Set WorkRng = Application.InputBox("Please select the range D/C", Type:=8)

        Dim WorkRng2 As Range
        Set WorkRng2 = Application.InputBox("Please select the range of amounts", Type:=8)

        If WorkRng.Cells.Count <> WorkRng2.Cells.Count Then MsgBox "Ranges must be same size"
    Loop Until WorkRng.Cells.Count = WorkRng2.Cells.Count

    Dim rng1 As Variant
    rng1 = WorkRng.Value

    Dim rng2 As Variant
    rng2 = WorkRng2.Value

    Dim i As Long
    For i = LBound(rng1, 1) To UBound(rng1, 1)
        rng2(i, 1) = rng2(i, 1) * IIf(rng1(i, 1) = "D", 1, -1)
    Next i

    WorkRng2.Value = rng2
End Sub

Upvotes: 3

Related Questions