Loop through Range

[St Jimmy]

When using Worksheet_Change event, I see that if the user pastes a range of cells at once the values are input as an array. How can I loop through the values of the array, but also catch if a single cell is changed?

What I have right now, this works for single cell changes but not pasted lists:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Target.Value2 = Function.blahblahblah

I'm trying to change it to this, but I cant get a handle on the formatting:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim tempValue As Variant

For Each TempValue in Target
     Target.Value2 = Function.blahblahblah
Next TempValue

Any help with syntax would be appreciated!

 

[Fluff]

How about

For Each TempValue in Target
     [COLOR=#ff0000]TempValue[/COLOR].Value2 = Function.blahblahblah
Next TempValue

 

[St Jimmy]

Hi Fluff, thanks for the quick reply.
When I try this TempValue is ""
How do I set the value2 of TempValue for the next target.value2?
Thanks!

 

[Fluff]

I've absolutely no idea, because you haven't given much to go on

Could you please describe what you are trying to do?

 

[St Jimmy]

Sorry about that, I have a sheet that automatically looks up information based on a user entry, lets call it vial.
I have a table that allows for the user to enter the vial to look up. I use Worksheet_change event to automatically format and calculate the input.
When there is a single cell that is changed the code works like a charm because I can just say Target.Value2 and use that to enter into the formatting and lookup routines.

Frequently (more often than not) a user will paste a list of vials into the sheet and when this happens it looks to me that excel treats the range as an array and I can see each of the appropriate values in the Watch window, but since it has multiple .value2's my code wont work. How can I get the code to either check if the range (target) is a single cell with a single .value2 or an acutal range (with an array of value2s)?

I'm not sure how to post screenshots, but I can if Im allowed...

 

[Fluff]

It doesn't matter whether it's a single cell or a range that has been changed, the 2nd code you posted will work for both.
It just depends on what your

Function.blahblahblah

actually is

 

[St Jimmy]

The function is a pre-processing and formatting routine expecting a single string to be passed (which is what value2 should be).
When multiple cell values are changed excel passes "" as the value2 instead of each value2 in the array.
My strategy is to try and selectively pass each single value2 from the array, but since I dont know how many there are to begin with I'd like to use a for each loop but Im extremely unfamiliar with them.

Below is what it looks like when 1 cell is changed (note value2 in watch)
https://drive.google.com/open?id=1DcqdWVvNNIiczWTA35zsuA4SIuJeHD6l

Below is what it looks like when multiple cells are changed (note value2 in watch)
https://drive.google.com/open?id=1t8UnnpZ7KDEfq_0VvAAmucpRYreY29-Y

How do I access each value2 individually in the second image?

 

[Fluff]

What is the function that you are using?

 

[St Jimmy]

Function VIALFormatter(inVIAL As String) As String
Dim startVIAL As String, tempVIAL As String
Dim MaxVIAL As Integer, CharCount As Integer, i As Integer
Dim Reagent As Boolean


startVIAL = inVIAL
If startVIAL = "" Then
    inVIAL = "-"
    GoTo ExitVialQA
End If
CharCount = Len(startVIAL)
If Left(startVIAL, 1) = "R" Then
    Reagent = True
    tempVIAL = "R"
Else
    Reagent = False
    tempVIAL = "L"
End If


For i = 1 To CharCount
    If IsNumeric(Mid(startVIAL, i, 1)) = True And Mid(startVIAL, i, 1) > 0 Then
        CharCount = (CharCount - i + 1)
        GoTo ExitLoop
    End If
Next i
ExitLoop:


For i = 1 To (7 - CharCount)
    tempVIAL = tempVIAL & "0"
Next i


tempVIAL = tempVIAL & Right(startVIAL, CharCount)


inVIAL = tempVIAL
VIALFormatter = inVIAL
ExitVialQA:


End Function

Formats the Vial appropriately for lazy bums like me.

 

[Fluff]

In that case try

For Each TempValue in Target
     TempValue.Value2 = Function.VIALFormatter(tempvalue.value)
Next TempValue