CODEWITHSUNDEEP
excelvba
Luiz Vaughan
Luiz Vaughan

Reputation: 675

Replicating cells in Excel VBA : Drop box and formula interaction

QUESTION 1: How to write a VBA code in order to accomplish this task and, most importantly, replicate the formulas to all cells of column N ?

Programming Algorithm
==================

The code will check if the word "Real" or "Dollar" or "Euro" is present in cells of column L. *If "Dollar" is there, then it will multiply constant Dollar times the number the user prompted* (value of goods) times the quantity in the cells of column M. The outcome will be placed in cells of column N, which are always in "Real"

If L13="Dollar" Then
N13=J13*M13*I$3

Else

If L13="Real" Then
N13=J13*M13

Else

If L13="Euro" Then
N13=J13*M13*I$4

Else

N13="0" and M13="0"

QUESTION 2: Also concerning drop boxes, suppose I want to have the total amount in column N and its cells will be added up only if they individually match some criteria. Here is the example where the user picks up a symbol inherited from a drop box (4 possible Wigdings symbols: I, &, x, þ)

Z1=SUMIF(B13:B300;"=þ";N13:N300)

(interval; criteria; interval sum). This means that the single cell Z1 encompasses the total amount of all cells pertaining to column N, with the condition that column B possesses the aforementioned symbols. Yet, this does not work. It Always gives me the value zero. Interesting, however, is to notice that if I replace (I, &, x, þ) by letters (a, b, c, d) the Z1 receives a non zero value. Not all letters work, as we can see by the letter "x" above.

Upvotes: 0

Views: 388

Answers (1)

Luiz Vaughan
Luiz Vaughan

Reputation: 675

Q1: SOLUTION

  Sub Invest()

  Application.ScreenUpdating = False

  Dim cell As Range

  Sheets("Invest").Select    


  Range("N13").Select
  ActiveCell.FormulaR1C1 = _
    "=(RC[-4])*(IF(RC[-2]=""Dolar"",RC[-1]*R3C[-5],IF(RC[-2]=""Real"",_
          RC[-1],IF(RC[-2]=""Euro"",RC[-1]*R4C[-5],AND(0,RC[-1]=0)))))"

this translates what is written above
=J13(SE(L13="Dolar";M13*I$3;SE(L13="Real";M13;SE(L13="Euro";M13*I$4;E(0;M13=0)))))*

  t = Cells(1048576, "B").End(xlUp).Row
  Selection.AutoFill Destination:=Range("N13:N" & t), Type:=xlFillDefault

The part above is very nice. It searches for data in column B until the last row of B1048576. If, for instance, column has some data till cell B300, then it will count how many cells there are between B300 and B13. Therefore, it will then copy the formula from N13 to N300. However, one should keep in mind that replication of formulas in a WorkBook full of sheets and several hundreds of rows can increase a lot the final size of the excel file. An alternative is to copy the outcome of the last calculation and paste special it in the same location, as value. As for example :

Range("N13:N" & t).Select
Selection.Copy
Range("N13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Q2: SOLUTION

Do not use "=". It will then be:

  Z1=SUMIF(B13:B300;"þ";N13:N300)

In an English version of MSOffice you will use commas instead of semicolon

Well, that´s it. See ya, Luiz

Upvotes: 0

Related Questions