CODEWITHSUNDEEP
excelvba
NCC
NCC

Reputation: 819

Make cell reference between a row and columns with a jump between columns using Excel VBA

I have tried before that make reference a row to columns/a column to rows. They are not perfect but they work. However, I have trouble when it jumps and I really need help.

enter image description here

My code doesn't return an error but only 1 cell has value "=".

enter image description here

Upvotes: 0

Views: 749

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

Does this work for you:

*added in factor that user needs reference to cells, not just cell values.

Sub Row2ColumnReferance()

Dim rRangeCopy As Range, CRangePaste As Range
Dim jump As Integer

'get input
Set rRangeCopy = Application.InputBox("Select Copy Range", "Transform", Type:=8)
Set CRangePaste = Application.InputBox("Select Destination Range", "Transform", Type:=8)
jump = Application.InputBox("Enter")

Dim cel As Range, intCnt As Integer

'place new cells
intCnt = 1
For Each cel In rRangeCopy
    CRangePaste.Cells(intCnt, 1).Formula = "=" & cel.Address(False, False)
    intCnt = intCnt + 1
Next

Dim intRows As Integer

'insert space
intCnt = CRangePaste.Rows.Count
For intRows = intCnt To 2 Step -1
    Range(CRangePaste.Cells(intRows, 1), CRangePaste.Cells(intRows + jump - 1, 1)).Insert shift:=xlDown
Next

End Sub

Upvotes: 1

Related Questions