Trying to transpose in an if then statement

Question

Large project, need help with a sub that is called out in another sub

This isnt outputting anything and the sub that calls it is working but not this part

`Private Sub Data100()

 Dim b1 As Integer
 Dim sourceRange As Range
 Dim targetRange As Range
 b1 = Range("V8").Value

If b1 = 0.015625 Then
Set sourceRange = Range("U150", "X150")
Set targetRange = Range("U201", "U204")
sourceRange.Copy
targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks:=False, Transpose:=True
End If

End Sub`

no error message, just wont output anything. Would like to copy U150-X150 and output in column U201-U204

You haven't specified the worksheet that your `Range`s are on. Implicitly they are on the `ActiveSheet`. Is that what you want? — BigBen, Jun 04 '19 at 20:27
You need to change your Range to be `Range("U150:X150")` or else you need to setup a UNION. Also, don't `pastevalues`. — pgSystemTester, Jun 04 '19 at 20:36
You also have an `Integer` that should be a `DOUBLE` if it can be a decimal. — pgSystemTester, Jun 04 '19 at 20:42

pgSystemTester · Accepted Answer · 2019-06-05T03:55:03.967

Upon further review, I was incorrect about the syntax range being defined incorrectly. I've never seen using Range("U150","X150") but it appears to be the same result as Range("U150:X150"). You get an uptick for this discovery!

Soo... the only thing wrong that I can see with your code that your if-statement is returning False because you have B1 defined as an Integer and your if statement is checking for being equal to a decimal which it will never be. If you redefine B1 as Double, I think your code will work as shown below. I ran this slightly modified code of yours successfully.

Also, avoid using Integer in general. Just use Long. See post here on some discoveries from the SO community. The memory savings isn't worth it.

Private Sub Data100_Modified()

 Dim b1 As Double, sourceRange As Range, targetRange As Range

With ActiveSheet

b1 = .Range("V8").Value


If b1 = 0.015625 Then
Set sourceRange = .Range("A1","A3")
Set targetRange = .Range("B2","d2")

sourceRange.Copy
targetRange.PasteSpecial Paste:=xlPasteValues, Transpose:=True

End If

End With

End Sub

Even though the original wasn't, some indentation would be a nice touch. — FreeMan, Jun 05 '19 at 10:15

Trying to transpose in an if then statement

1 Answers1