When using an IF statement the output keeps returning the same numbers for everything?

Question

I'm trying to write some code which changes each values in a range into a corresponding value from two columns. For example if a value in my range is €29.99. It looks at a table and at column 1 if it finds a value equal to it then changes that cell in the range into the value adjacent in column 2, £24.99.

My problem is the code I have written always returns the same value for all cells the macro affects, the lowest value in the column 2(£9.99). So for example even the values which are €59.99, 29.99 etc are being changed to £9.99. I don't know why this is happening? Or how to resolve it?

€ RRP is column 1 and £ RRP is column 2

This is an example of what is in 'digital €'

Any help would be greatly appreciated. My code is as follows

Sub EurosToPounds()

Dim rng As Range

If Sheets("Pricing Matrix").Range("InPounds") = "TRUE" Then
Exit Sub

ElseIf Sheets("Pricing Matrix").Range("InPounds") = "False" Then

For Each rng In Worksheets("Digital €").Range("Digital")
  If rng = Range("Euro1") Then
      rng.Value = Range("Euro1").Offset(0, 2).Value
  ElseIf rng = Range("Euro2") Then
      rng.Value = Range("Euro2").Offset(0, 2).Value
  ElseIf rng = Range("Euro3") Then
      rng.Value = Range("Euro3").Offset(0, 2).Value
  ElseIf rng = Range("Euro3") Then
      rng.Value = Range("Euro3").Offset(0, 2).Value
  ElseIf rng = Range("Euro4") Then
      rng.Value = Range("Euro4").Offset(0, 2).Value
  ElseIf rng = Range("Euro5") Then
      rng.Value = Range("Euro5").Offset(0, 2).Value
  ElseIf rng = Range("Euro6") Then
      rng.Value = Range("Euro6").Offset(0, 2).Value
  ElseIf rng = Range("Euro7") Then
      rng.Value = Range("Euro7").Offset(0, 2).Value
  ElseIf rng = Range("Euro8") Then
      rng.Value = Range("Euro8").Offset(0, 2).Value
  ElseIf rng = Range("Euro9") Then
      rng.Value = Range("Euro9").Offset(0, 2).Value
  ElseIf rng = Range("Euro10") Then
      rng.Value = Range("Euro10").Offset(0, 2).Value
  ElseIf rng = Range("Euro11") Then
      rng.Value = Range("Euro11").Offset(0, 2).Value
  ElseIf rng = Range("Euro12") Then
      rng.Value = Range("Euro12").Offset(0, 2).Value
  End If
Next

 Sheets("Digital €").Select
 Sheets("Digital €").Range("Digital").Select
 Selection.NumberFormat = _
    "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InEuros") = "TRUE"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InPounds") = "FALSE"

End If
End Sub

This code causes the numbers to change to...

Another method suggested is as follows...

Sub EurosToPounds()

Dim rng As Range

If Sheets("Pricing Matrix").Range("InPounds") = "TRUE" Then
Exit Sub

ElseIf Sheets("Pricing Matrix").Range("InPounds") = "False" Then

For Each rng In Worksheets("Digital €").Range("Digital")
Dim foundRange As Range
Set foundRange = Range("EuroRange").Find(rng.Value) ' EuroRange should contain (
'                                                     Euro1, Euro2,.. etc).
If Not foundRange Is Nothing Then rng.Value = foundRange.Offset(0, 2).Value
Next

Sheets("Digital €").Select
Sheets("Digital €").Range("Digital").Select
Selection.NumberFormat = _
    "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InEuros") = "TRUE"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InPounds") = "FALSE"

End If
End Sub

However that results in this...

I feel as tho the second method may not be working as "Digital €" contains formulas in some of the cells equaling the cell to the left of it maybe? Is there a way to ignore cells with formulas in, that may solve it. Again thanks for any help!

Have you tried stepping through your code using F8 to monitor the values of variables? — SJR, Jan 10 '17 at 17:47
This is difficult to answer without knowing what the named ranges refer to. An annotated screenshot of the worksheet "Digital €" would help tremendously. — Comintern, Jan 10 '17 at 18:35
Thanks, sorry didnt realise how easy it was to add images. Effectively what is happening when I'm running the macro is that the majority of the numbers in 'Digital €' go down to 9.99. — R. Patrick-White, Jan 11 '17 at 10:10

score 0 · Answer 1 · answered Jan 10 '17 at 20:42

0

rng is declared as Range. So you have to compare its value and not the object it self.

Like :

If rng.value = Range("Euro1").value Then ...

answered Jan 10 '17 at 20:42

D. O.

616
1
11
25

I added '.Value' however it made no difference. I was under the impression that .Value was the default so putting nothing is the same as (might be wrong tho I'm relatively new to VBA) – R. Patrick-White Jan 18 '17 at 15:27
Sure, but the Default Property of Range is Value, so you can omit it. – z32a7ul Jan 18 '17 at 15:44

score 0 · Answer 2 · answered Jan 18 '17 at 15:52

0

I think in this line the OP wants to decide if the two variables refer to the same range:

If rng = Range("Euro1") Then

However, this way you compare the Value of rng and Range("Euro1"), and maybe only that of the first cell. So if rng refers to Euro2 but the first cells of Euro1 and Euro2 contain the same values, then the second cell of Euro1 will be used instead of Euro2.

Solution: compare Ranges with Is or their address:

If rng Is Range("Euro1") Then ' Version A
If rng.Address = Range("Euro1").Address Then ' Version B - assuming that all cells in question are on the same sheet

answered Jan 18 '17 at 15:52

z32a7ul

3,695
3
21
45

In that line I'm trying to check if the the first cell in 'Digital €' is the same as the first cell in the € RRP column, if it is then it changes the value of the of the first cell in 'Digital €' to that of the first cell in the £ RRP column. I've just tried changing the code to both versions you suggested however now the numbers don't change at all – R. Patrick-White Jan 19 '17 at 09:32
Put a break point (F9) next to the If and run the code. When it hits the break point, type the following in to the Immediate Window (Ctrl+G): Print rng.Address [Enter] Print Range("Euro1").Address [Enter] – z32a7ul Jan 19 '17 at 15:09

score 0 · Answer 3 · edited May 23 '17 at 12:33

Well, as you said in the comments, Value is the default member of the object Range. Hence:

Yes, you can use Range instead of Range.Value, and that won't be a problem when you're checking for value. But when you're changing the value of a range, you have to be careful about the type of the variable you use to set the value. Check this answer for more information.

Now, when you use rng = Range("YourRange").Offset(0, 2).Value, you're not actually changing the value of your range (cell), and that's because the variable rng is a range. To actually change the value of the cell/range, you should use:

rng.Value = Range("YourRange").Offset(0, 2).Value ' The second ".Value" is optional
'                                                   while the first one is mandatory.

Hence, your code using your current method should look like the following:

For Each Rng In Worksheets("Digital €").Range("Digital")
    If Rng = Range("Euro1") Then
        Rng.Value = Range("Euro1").Offset(0, 2).Value
    ElseIf Rng = Range("Euro2") Then
        Rng.Value = Range("Euro2").Offset(0, 2).Value
    ' Your remaining cases
    '
    End If
Next

However, using If.. Then.. ElseIf to check for all twelve cases isn't a good practice. Instead, you should be using Range.Find to look for a matching value in a specified range. Something like the following is all what you need:

For Each Rng In Worksheets("Digital €").Range("Digital")
    Dim foundRange As Range
    Set foundRange = Range("EuroRange").Find(Rng.Value) ' EuroRange should contain (
    '                                                     Euro1, Euro2,.. etc).
    If Not foundRange Is Nothing Then Rng.Value = foundRange.Offset(0, 2).Value
Next

Hope that helps :)

Hi Ahmed, thanks for the clarification and for the answer. The top method you proposed when I run it, it returns an invalid property error on the second rng.Value? — R. Patrick-White, Jan 19 '17 at 11:13
@R.Patrick-White If all the information you provided in your question is accurate, both methods should work just fine. Which method did you use? Please make sure your code is exactly the same as one of the above methods (*I suggest the second one*), or edit the question and add your updated code so we can help you. — 41686d6564 stands w. Palestine, Jan 19 '17 at 12:19
@R.Patrick-White, here's a live example testing both methods: http://g.recordit.co/27zepmcraL.gif — 41686d6564 stands w. Palestine, Jan 19 '17 at 12:20

When using an IF statement the output keeps returning the same numbers for everything?

3 Answers3