Changing the Value of a Range 1

[Mizzness]

How can you change the value of a range ?
I want to apply this funtion to a button.

Example: Range "B39" has certain values.
Cell "A2" = 1.5
I want to multiply the Range by the Cell to give me a new set of values for this range.

Can this be done ?

Thanks.

 

[VBAjedi]

Well, there's a bunch of ways to do this. One would be:

Sub AdjustValues()
Dim myCell as Range
For Each myCell in Range("B3:D9")
   myCell.Value = myCell.Value * Range("A2").Value
Next myCell
End Sub

Another way uses Excel's "Paste Special" command, which allows you to multiply the selected range's values by the value you copied:

Sub AdjustValues2()
    Range("A2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B3:D9").Select
    Selection.PasteSpecial Paste:=xlAll, _
      Operation:=xlMultiply, SkipBlanks:= _
      False, Transpose:=False
End Sub

Let me know if this does what you want!

VBAjedi

 

[jcrater]

Dim cel As Range
Dim rng As Range

Set rng = Range("B39&quot
For Each cel In rng
cel.Value = cel.Value * Cells(2, 1).Value
Next cel

 

[Mizzness]

VBAJedi,

Thanks for your help, most helpful indeed.

I'm new to VBA programming & possibly you can help me with another issue.

Can you add/delete the number of columns based on the change of data in a cell ?

Example: cell data is changed from 4 to 6, this would increase my columns from 4 to 6 (formulas incl.).

I've heard this is called a Change-Event procedure.

Thanks for any guidance.

 

[VBAjedi]

Mizzness,

I'm not exactly sure from your post what you are after. But the following code does what I think you are asking. It assumes that cell "A1" is the controlling cell. Entering "2" into that cell will add two columns before column "B". Entering "-2" into that cell will DELETE two columns starting with column "B". Modify the following code to suit what you are after, then place in the "Sheet1" (or whatever your sheet is named) object under "Microsoft Excel Objects" in the VBA project window.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ColsToChange As Integer
Dim x As Integer

If Target.Row = 1 And Target.Column = 1 Then ' Cell "A1" changed
   If IsNumeric(Range("A1").Value) Then
      ColsToChange = Int(Range("A1").Value)
   End If
   
   If ColsToChange < 0 Then
      ColsToChange = Abs(ColsToChange) ' Change it back to a positive number
      Range(Cells(2, 2), Cells(2, (ColsToChange + 1))).EntireColumn.Delete
   Else
      For x = 1 To ColsToChange
         Range(&quot;B1&quot;).EntireColumn.Insert
      Next x
   End If
End If
End Sub

This code uses the Worksheet_Change event, but you might be interested in seeing all the events available to you for the sheet object. Just go to the little drop-down box at the top-right of the VBA window that says "Worksheet_Change" right now. It contains the other events.

Let me know if that's what you are looking for!


VBAjedi

 

[Mizzness]

VBAJedi,

You're right "A1" is the controlling cell in one worksheet.
By changing its data, I want to add/delete columns in another worksheet.

Example:

Worksheet "summary", cell "A1" = 4
Worksheet "detail" has data in four columns (last column being totals).

By making "A1" = 6, this will increase my columns in the "detail" worksheet to six. Hence if "A1" = 3, my columns will decrease by one.
I would like this program to add/delete to the left of the totals column.

I hope I clarified enough.
Thanks for any help.

 

[VBAjedi]

Ok - slightly modified version of my code should work. Select the header cell of your Totals column on the Detail sheet and name it "DetailTotals". Then put this code in the Summary sheet object in the VBA editor:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ColsToChange As Integer
Dim x As Integer

If Target.Row = 1 And Target.Column = 1 Then ' Cell "A1" changed
   If IsNumeric(Range("A1").Value) Then
      ColsToChange = Int(Range("A1").Value)
   End If
   
   If ColsToChange < 0 Then
      For x = 1 To Abs(ColsToChange) ' Change it back to a positive number
         Worksheets(&quot;detail&quot;).Range(&quot;DetailTotals&quot;).Offset(0, -1).EntireColumn.Delete
      Next x
   Else
      For x = 1 To ColsToChange
         Worksheets(&quot;Detail&quot;).Range(&quot;DetailTotals&quot;).EntireColumn.Insert
      Next x
   End If
End If
End Sub

Let me know how that works!

VBAjedi

 

[Mizzness]

VBAJedi,

Did I make a mistake ? Your instructions were clear but something is amiss.

Using controlling cell "E5" in my "summary" worksheet and Range "C3:F6" with "DetailTotals" as the header for column "F" in "Detail" worksheet, using your formula here is my program:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ColsToChange As Integer
Dim x As Integer

If Target.Row = 1 And Target.Column = 1 Then ' Cell "E1" changed
If IsNumeric(Range("E1&quot.Value) Then
ColsToChange = Int(Range("E1&quot.Value)
End If

If ColsToChange < 0 Then
For x = 1 To Abs(ColsToChange) ' Change it back to a positive number
Worksheets(&quot;detail&quot.Range(&quot;DetailTotals&quot.Offset(0, -1).EntireColumn.Delete
Next x
Else
For x = 1 To ColsToChange
Worksheets(&quot;Detail&quot.Range(&quot;DetailTotals&quot.EntireColumn.Insert
Next x
End If
End If

End Sub

Thanks for any help in solving this.

 

[VBAjedi]

Mizzness,

Been out of town. . .

If you still haven't got this working yet, here's a thought. You said you were using cell &quot;E5&quot; as your controlling cell, but your code shows that you are (mostly) looking at cell &quot;E1&quot; (the exception being that you forgot to update the Target.Row statement to see if the row that changed was row #5). Assuming that you are using &quot;E5&quot; as your key then you need to change the references in the first few lines as follows:

If Target.Row = 5 And Target.Column = 5 Then ' Cell "E5" changed
   If IsNumeric(Range("E5").Value) Then
      ColsToChange = Int(Range("E5").Value)
   End If

Notice that since you are checking Target.Row and Target.Column to make sure the key cell is the one that changed, you need to update both of these values in your &quot;If. . .&quot; statement if you change your key cell's location.


VBAjedi

 

[Mizzness]

VBA Jedi,

Thanks for the help regarding my last aaplication.

Hopefully you can help me with this task:
I would like to create a macro that compares the value of my target cell to a range of designated cells.

My example is as follows:
Target Cell (F1) = 100
Cell (X1)= 101
Cell (Y1)= 102
Cell (Z1)= 103
Cell (AA1)= 99.5

I would like the macro look up the cells in my range (X1:AA1) and have the closest value (99.5) to my target cell (F1) pasted in a seperate tab labeled &quot;Closest Value&quot;.
These are random numbers in no seuential order as my real numbers will be.

Can this be done ?

Thanks for any and all help.
I only recently started using VBA but some tasks are more difficult than others.

 

[Zathras]

Mizzness: This is almost the same question you asked in thread707-571166 which I answered there. Why are you asking it again here?