CODEWITHSUNDEEP
vbatranspose
Andrew Pettit
Andrew Pettit

Reputation: 37

Trying to transpose in an if then statement

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

Upvotes: 2

Views: 163

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

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

Upvotes: 1

Related Questions