update a column of numbers from value to (value * 1.3)
johnn
I want to update a columns("B") of numbers to +30%
I know I have solutions like a VBA "Loop" or an ADODB "UPDATE Query", But for some reason I can only use Excel Formula.
So, I wrote VBA code like
Columns("B").FormulaLocal = "=INDIRECT(ADDRESS(ROW(), COLUMN()))*1.3"
And I got all "0" in Columns("B"). I know the formula may case "circular reference" problem, but is there a way to calculate "*1.3" only once?...Thx
user6432984
You can use
You can use PasteSpecial to multiple the values.
Sub DirtyUpdate()
Dim temp As Variant
With Range("A1")
temp = .Value
.Value = 1.3
.Copy
Intersect(Columns("B"), ActiveSheet.UsedRange).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
.Value = temp
End With
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
Update column from another table column value
- 2
Get value in column from matrix of row numbers
- 3
MySQL update column value with max value from another column
- 4
Update column for random value from different table
- 5
Update column with value from two potential columns
- 6
Update certain column of recordset from textbox value
- 7
mysql update column value from another table
- 8
Excel - How to fill in incrementing numbers in column starting from 1 until the value in another columns stays the same
- 9
UPDATE sql column with value from another column based on a date column
- 10
Update column value with trigger postgresql/ fetch value from insert query
- 11
Column value incremental update
- 12
Update a column with a calculated value
- 13
update DataGridView column value
- 14
update a column with derived value from another column in mysql
- 15
Update multiple column value from one column of the same table
- 16
Update a column by selecting random value from a different column
- 17
update a column with derived value from another column in mysql
- 18
Column loop and update another column with COUNT() value from another table
- 19
Fetch XML value from one column and update it in another column
- 20
Update multiple column value from one column of the same table
- 21
reset column value from 1 to count
- 22
Get full value from column char(1)
- 23
Update column to a value in joined column
- 24
Getting smallest value from column that holds numbers as strings
- 25
Yii2: not able to update column value by + 1
- 26
UPDATE first column with min value LIMIT 1 with INNER JOIN, in PHP
- 27
Yii2: not able to update column value by + 1
- 28
R if value in column 1 in table a matches value in column 1 in table b, copy value from column 2 in table b into table 1
- 29
If column 1 is a match, change value of column 3 using Awk
Comments