Set Values of each Cell in a Range

I am working in Excel VBA and am trying to set each value in a Named (Named_Range) Range.

I have 2 question related to this:
1) How to refer to each Cell in the Range; &
2) Can this be done as a ByiProduct of the executionof a Function?

|

You can use a for loop

Dim m As Range

For Each m In Range("<name>")

   m.Value = <whatever>

Next

 

To select specific Cells, Cells is a valid property of a range object:

Range("<name>").Cells(1, 1) Refers to the first cell in the range.  You can also use this in a For loop to get to every cell

For i = 1 To Range("<name>").Rows.Count

For j = 1 To Range("<name>").Columns.Count

Range("<name>").Cells(i, j).Value = <whatever>

Next j

Next i

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi,

When you say set the value, do you mean entering data in a blank cell?  Also, it is unclear what you mean by by product of a function?  A function only affects the cell it is in, changing its value.  Functions can't operate on the GUI to change anything other than the cell they are entered in.

For Each cell in Range("Named_Range")

'code to set value of cell here

Next cell

 


If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire
Shane Devenshire

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The trouble may be related t the fact that I have to put a different value in each Column & Row o the target Range.
Also, I need the format o the statement which sets the value.
I'm having a problem with the phrasing of the statement.
I need to Select the Range & then have statements which sets each value separately.
The Range also has to be 2 Columns & 5 Rows.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The trouble may be related t the fact that I have to put a different value in each Column & Row o the target Range.
Also, I need the format o the statement which sets the value.
I'm having a problem with the phrasing of the statement.
I need to Select the Range & then have statements which sets each value separately.
The Range also has to be 2 Columns & 5 Rows.

Simply change the second loop I recommended so it isn't a loop.

Range("<namedrange>").Cells(1,1).Value = <whatever>

Range("<namedrange>").Cells(1,2).Value = <whatever>

Range("<namedrange>").Cells(2,1).Value = <whatever>

Range("<namedrange>").Cells(2,2).Value = <whatever>

Range("<namedrange>").Cells(3,1).Value = <whatever>

Range("<namedrange>").Cells(3,2).Value = <whatever>

Range("<namedrange>").Cells(4,1).Value = <whatever>

Range("<namedrange>").Cells(4,2).Value = <whatever>

Range("<namedrange>").Cells(5,1).Value = <whatever>

Range("<namedrange>").Cells(5,2).Value = <whatever>

 

 

I'm not sure what "format the statement which sets the value" means.  If the above isn't what you're looking for, please tell us exactly what it is you're trying to do.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

This is what I tried, but it cause the unction it's in to return "#VALUE!" whether the Optional Argument is present or not & without the "Set" it causes Excel to crash!

Also, Do I really need the "s? Cause they aren't clear on how to use them!!!!!

'    If IsMissing(Diamond_Range) = False Then
'        Set Diamond_Points.Value2(1, 1) = F_3(x_value)
'        Set Diamond_Points.Value2(1, 2) = F_3(y13)
'        Set Diamond_Points.Value2(2, 1) = F_3(x2)
'        Set Diamond_Points.Value2(2, 2) = F_3(y_value)
'        Set Diamond_Points.Value2(3, 1) = F_3(x34)
'        Set Diamond_Points.Value2(3, 2) = F_3(y13)
'        Set Diamond_Points.Value2(4, 1) = F_3(x2)
'        Set Diamond_Points.Value2(4, 2) = F_3(y4)
'        Set Diamond_Points.Value2(5, 1) = F_3(x_value)
'        Set Diamond_Points.Value2(5, 2) = F_3(y13)
'    End If

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Am 14.09.2010 00:14, schrieb cimofj2:

Also, Do I really need the "s? Cause they aren't clear on how to use them!!!!!

As said before: Show us the whole code. Otherwise we can play guessing games for days.

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

None of this worked for me!!!!!

I made a rather simplistic example & tried all the various combinations!

Function Arg_Range_Test_2(R As Variant, Optional P As Variant) As Variant
    Arg_Range_Test_2 = R.Cells(1, 1)
    If IsMissing(P) = False Then
        ' Actions
        P.Cells(1, 1).Value = 10
    End If
    End Function

NOTE: If the "Optional" Range is added, ALL attempts return "#VALUE!" as a result & the Result of the 1st executable statement is ignored!!!!!

 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I can not understand what you really want to do, I guess that you want to create a UDF and you have problems in the evaluation of the function parameters?

(A UDF is a "user definied function" which is called from a sheet via a formula.)

At first, you can not change any cell inside a UDF, if the UDF runs the whole workbook is writeprotected. You can only pass a result over the function itself.

If the result is a single value you can pass the value directly to the function. If you want to return 2 or more values you must write any value to an array created inside that function and pass this array as result to the function and the function must be called as a matrix formula.

At second, if your input range has 2 columns and 5 rows you should pass the whole range to the UDF and test inside the UDF is the range has the expected size. If you did it not this way (as your last example above), excel can not determine in which situations your UDF must be called and your UDF becomes volatile.

Function ArgTest(R As Range, Optional P As Variant) As Variant
  Dim I As Long, J As Long
  Dim Result()
  'Test input size
  If R.Rows.Count < 5 Or R.Columns.Count < 2 Then
    'If it is to small show #REF
    ArgTest = CVErr(xlErrRef)
    Exit Function
  End If
  'Output size = input size
  ReDim Result(1 To R.Rows.Count, 1 To R.Columns.Count)
  'Visit each cell
  For I = 1 To R.Rows.Count
    For J = 1 To R.Columns.Count
      Result(I, J) = R(I, J)
    Next
  Next
  'Test if P is given
  If Not IsMissing(P) Then
    'Is P a range or an array?
    If (VarType(P) And vbArray) = vbArray Then
      If TypeOf P Is Range Then
        'It's a range
        For I = 1 To P.Rows.Count
          For J = 1 To P.Columns.Count
            If I <= UBound(Result) Or J <= UBound(Result, 2) Then
              Result(I, J) = P(I, J)
            End If
          Next
        Next
      Else
        'It's a array
        For I = 1 To UBound(P)
          For J = 1 To UBound(P, 2)
            If I <= UBound(Result) Or J <= UBound(Result, 2) Then
              Result(I, J) = P(I, J)
            End If
          Next
        Next
      End If
    Else
      'A value
      Result(1, 1) = P
    End If
  End If
  'Return result
  ArgTest = Result
End Function

Write the formula
  =ADDRESS(ROW(),COLUMN())
in each cell in A1:B6

And each of this formulas as matrix formula in a 5x2 block of cells.

=ArgTest(A1:B4)
=ArgTest(A1:B5,"Hello")
=ArgTest(A1:B5,A6:B6)
=ArgTest(A1:B5,{"a";"b";"c"})

Andreas.

PS.:  I'm very busy time for the next two weeks, my answer can take a while.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I have found that the following works for what I wanted, but unfortunately you still need the 1st 5 lines to get the Range info;

Function Any_Function(Any_Range As Variant) As String
    First_Column = Any_Range.Column
    First_Row = Any_Range.Row
    Last_Column = Range_Last_Column(Any_Range)
    Last_Row = Range_Last_Row(Any_Range)
    Total_Cells = Any_Range.Count
    Any_Function = ""
    Column = First_Column

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Why would you like to know which Column or Row AnyRange has?

 

You can visit each cell in that range by

 

for y = 1 to AnyRange.Rows.Count

for x = 1 to AnyRange.Columns.Count

  Debug.Print AnyRange(Y,X).Value

next

next

 

But this works only if AnyRange has only one area. If there are more areas in that range you must visit each area or use a simple FOR EACH:

 

for each R in AnyRange

  Debug.Print R.Value

next

 

The next problem in a UDF is that a Variant parameter can hold a value or array too:

 

=Any_Function(1)
=Any_Function({1;2;3})

 

So be sure that the given parameter is a range.

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated March 26, 2018 Views 2,160 Applies to: