logo
down
shadow

How to run xlswrite with formula in loop function


How to run xlswrite with formula in loop function

By : Zoltan Szalas
Date : November 20 2020, 03:01 PM
may help you . You can use sprintf:
code :
for II = 1:3
  out{II} = sprintf('=SUM(B%u:C%u)', II, II);
end
out =

  1×3 cell array

    {'=SUM(B1:C1)'}    {'=SUM(B2:C2)'}    {'=SUM(B3:C3)'}


Share : facebook icon twitter icon
Insert Formula into Cell where Part of the Formula Depends on Value in Loop

Insert Formula into Cell where Part of the Formula Depends on Value in Loop


By : user2559374
Date : March 29 2020, 07:55 AM
wish help you to fix your issue As my dad always says, if you find yourself banging your head against the wall long enough, something good might slip in while the blood slips out.
I just started trying random things all with varying errors. I ultimately just dimmed a string for the value inside the problem cell and added the "+ 0.1" at that point. Then called the string on the formula line. Since I put it within the loop, it changes to the correct cell value on each subsequent iteration. Worked fine.
code :
                With ActiveSheet
                .Name = c
                Divisor = c.Offset(0, 6) + 0.1
                lrow = .Range("A" & .Rows.Count).End(xlUp).Row
                .Range("K2:K" & lrow).Value = c.Offset(0, -1)
                .Range("L2:L" & lrow).Value = c
                .Range("M2:M" & lrow).Value = c.Offset(0, 1)
                .Range("N2:N" & lrow).Value = ("=ROW($N2)-1")
                .Range("O2:O" & lrow).Formula = "=INT($N2/" & Divisor & ")+1"
                .Range("P2:P" & lrow).Value = ("=COUNTIF($O:$O,$O2)")
                .Range("Q2:Q" & lrow).Value = ("=IF($P2<100,$O2-1,$O2)")
                .Range("R2:R" & lrow).Value = ("=CONCATENATE(K2,"" "",Q2)")
                .Range("S2:S" & lrow).Value = ("=CONCATENATE(""CA_Full_"",RC[-7],"" "",RC[-2],""_"",'X-User Input'!R4C2)")
for loop and a function combined to calculate a formula and then a regression

for loop and a function combined to calculate a formula and then a regression


By : techrebel86
Date : March 29 2020, 07:55 AM
With these it helps A little confused with how I am trying to acheive the results I want.
code :
# Put your data in a list:    
dflist = mget(paste0("df", 1:5))

# Apply your function to every data frame
ylist = lapply(dflist, function(x) x - R)

# Name the resulting columns y1:y5
ylist = lapply(ylist, setNames, paste0("y", 1:5))
Error in function to create en excel formula and pass this formula to module f2()

Error in function to create en excel formula and pass this formula to module f2()


By : user3352020
Date : March 29 2020, 07:55 AM
hop of those help? Here I want the function f1() to create en excel formula and pass this formula to f2() without returning back to f1() and the f2() replaces function with the formula, while doing so I am getting compile error: Expected in line Sub f2( Optional endAll As Boolean = False, jR1 as Long, jC1 as Long, jGetFormula as String) , move optional parameter to the end of declaration.
code :
Sub f2(jR1 As Long, jC1 As Long, jGetFormula As String, Optional endAll As Boolean = False)
VBA If Statement Formula in a Loop and Range Function to Get Cell Value

VBA If Statement Formula in a Loop and Range Function to Get Cell Value


By : Prince
Date : March 29 2020, 07:55 AM
should help you out
You need .Formula instead of .FormulaR1C1 - you are not using R1C1 references.
code :
Sub Test()
    Dim myFormula As String
    myFormula = "=IF(AND(J5>=K5,J5<=L5),""PASS"",""FAIL"")"

    Dim a As Long
    For a = 5 To 789 Step 16
        Range("M" & a).Formula = myFormula
    Next
End Sub
Function to Calculate Formula Without Using a For Loop in PL/SQL

Function to Calculate Formula Without Using a For Loop in PL/SQL


By : user3503999
Date : March 29 2020, 07:55 AM
will help you Perhaps is better to call one single query with the last 8 years and put the result on an array and so loop on it, without execute 8 query:
code :
DECLARE
  TYPE arrayofnumbers IS TABLE OF NUMBER(11);
  sums arrayofnumbers;
BEGIN
  SELECT NVL(SUM(amount), 0)
  INTO sums
  FROM moneytable g
    WHERE g.id_number = idnum AND g.fiscal_year between currentyear and currentyear+7;

 FOR i IN 1 .. sums.count
  LOOP
    -- other code
    dbms_output.put_line(sums(i));

  END LOOP;
END;
shadow
Privacy Policy - Terms - Contact Us © voile276.org