if cell has a value then copy and paste whats in that cell and to the right of it

[drop05]

Hello, a bit new to this.

1. Using VBA im trying to see if there is a way to check if a cell has a value, in my case cell G52, if it has a value then copy that cell and the cells to the right of it, so G52 H52 I52. . so on until there is a the last value.

2. Those values that are copied, paste them into another sheet/workbook in their landing spot. So say sheet two the first landing spot from the value in G52 will go in cell F163 then the value from H52 will go in cell F174, and the value from I52 will go into cell F185, and so on, basically pasting starting from the first landing spot of 163 and pasting the next value after every 11 cells below that.

3. Now back to part 1, If it does not have a value then skip it and go to the next iteration and do the same as above. The next iteration is about 351 cells below it, so G403, and basically would want it to do the same, if it has a value then copy that cell and everything to the right of it and paste it into the next iteration of sheet two. Now the 2nd iteration of sheet two those would not b F163 instead just replacing the F with G, same concept just different column per iteration

any help would be grateful!

 

[offthelip]

try this code:

Sub test()
For i = 0 To 1
lastcol = Cells(52 + i * 351, Columns.Count).End(xlToLeft).Column
inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))
    If inarr(1, 1) <> "" Then
        With Worksheets("Sheet2")
          For j = 1 To UBound(inarr, 2)
           jj = j - 1
            .Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)
           Next j
        End With
    End If
Next i
End Sub

 

[drop05]

try this code:

Sub test()
For i = 0 To 1
lastcol = Cells(52 + i * 351, Columns.Count).End(xlToLeft).Column
inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))
    If inarr(1, 1) <> "" Then
        With Worksheets("Sheet2")
          For j = 1 To UBound(inarr, 2)
           jj = j - 1
            .Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)
           Next j
        End With
    End If
Next i
End Sub

Hello, thank you for this! Very helpful

I am getting a run time error '13' type mismatch at
If inarr(1, 1) <> "" Then

cannot identify why that would happen.
I places a value in G52 and h52 to test

any idea?

Also would i use a xltoleft or xltoright, as i want the values from g52 and to the right of it

 

[offthelip]

The equation for last col should give you the first column that has data in it starting back from the far right hand side of the workhseet. When I put values in G52 and h52 it comes back with 8 which is correct ( column H)
The line you get an error on will occur if you only have a single value in G52 and not in h 52, becuase this will mean that inarr wil be created as a single variant variable and not as a variant array, and so the indexing in the equation (1,1) wil cause the error.
So check that you have got a value in H52 and if you have a vlue in g403 make sure you have one in h403 .
alternatively we can put a check in to ensure the are columns to the right of G52 like this:

Sub test()
For i = 0 To 1
lastcol = Cells(52 + i * 351, Columns.Count).End(xlToLeft).Column
If lastcol > 7 Then
inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))
    If inarr(1, 1) <> "" Then
        With Worksheets("Sheet2")
          For j = 1 To UBound(inarr, 2)
           jj = j - 1
            .Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)
           Next j
        End With
    End If
End If
Next i
End Sub

 

[drop05]

I see, makes sense, it pasted.
Question, when i want to paste in a second iteration values from the second iteration which are the ones that are from sheet one that are 351 cells apart. Those values from G403 and to the right, h403 i403 etc, would want them to paste in sheet two in h52

I tested some stuff and could not get it to paste in those areas
Same as when posting in g52 but the second iterations
The third iteration would be the same thing just i52 from there if that makes senss

The equation for last col should give you the first column that has data in it starting back from the far right hand side of the workhseet. When I put values in G52 and h52 it comes back with 8 which is correct ( column H)
The line you get an error on will occur if you only have a single value in G52 and not in h 52, becuase this will mean that inarr wil be created as a single variant variable and not as a variant array, and so the indexing in the equation (1,1) wil cause the error.
So check that you have got a value in H52 and if you have a vlue in g403 make sure you have one in h403 .
alternatively we can put a check in to ensure the are columns to the right of G52 like this:

Sub test()
For i = 0 To 1
lastcol = Cells(52 + i * 351, Columns.Count).End(xlToLeft).Column
If lastcol > 7 Then
inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))
    If inarr(1, 1) <> "" Then
        With Worksheets("Sheet2")
          For j = 1 To UBound(inarr, 2)
           jj = j - 1
            .Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)
           Next j
        End With
    End If
End If
Next i
End Sub

The equation for last col should give you the first column that has data in it starting back from the far right hand side of the workhseet. When I put values in G52 and h52 it comes back with 8 which is correct ( column H)
The line you get an error on will occur if you only have a single value in G52 and not in h 52, becuase this will mean that inarr wil be created as a single variant variable and not as a variant array, and so the indexing in the equation (1,1) wil cause the error.
So check that you have got a value in H52 and if you have a vlue in g403 make sure you have one in h403 .
alternatively we can put a check in to ensure the are columns to the right of G52 like this:

Sub test()
For i = 0 To 1
lastcol = Cells(52 + i * 351, Columns.Count).End(xlToLeft).Column
If lastcol > 7 Then
inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))
    If inarr(1, 1) <> "" Then
        With Worksheets("Sheet2")
          For j = 1 To UBound(inarr, 2)
           jj = j - 1
            .Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)
           Next j
        End With
    End If
End If
Next i
End Sub

 

[offthelip]

the code should work for two iterations, the line at the start:

For i = 0 To 1

means the code goes through twice once with the value of i =0 and the second with i=1, this means that this line:

inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))

picks up the values in row 52 the first time (i=0 therefore +i*351=0, so this statement is equivalent to :

inarr = Range(Cells(52 , 7), Cells(52 , lastcol))

the first iteration,
the second iteration i=1 so 52+i*351= 403 so the statement is equivalent to

inarr = Range(Cells(403 , 7), Cells(403 , lastcol))

the output will go into successive columns because of a similar use of the index "i"
note in this statement:

.Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)

it is referencing cells 6+1 in the column, this means the first iteration it goes in column 6 (column F) and the second iteration it goes in column 7 (column G)
to go round more iterations just change the loop

For i = 0 To 2

to get 3 iterations ( 0,1,2)

 

[drop05]

The equation for last col should give you the first column that has data in it starting back from the far right hand side of the workhseet. When I put values in G52 and h52 it comes back with 8 which is correct ( column H)
The line you get an error on will occur if you only have a single value in G52 and not in h 52, becuase this will mean that inarr wil be created as a single variant variable and not as a variant array, and so the indexing in the equation (1,1) wil cause the error.
So check that you have got a value in H52 and if you have a vlue in g403 make sure you have one in h403 .
alternatively we can put a check in to ensure the are columns to the right of G52 like this:

Sub test()
For i = 0 To 1
lastcol = Cells(52 + i * 351, Columns.Count).End(xlToLeft).Column
If lastcol > 7 Then
inarr = Range(Cells(52 + i * 351, 7), Cells(52 + i * 351, lastcol))
    If inarr(1, 1) <> "" Then
        With Worksheets("Sheet2")
          For j = 1 To UBound(inarr, 2)
           jj = j - 1
            .Range(.Cells(163 + jj * 11, 6 + i), .Cells(163 + jj * 11, 6 + i)) = inarr(1, j)
           Next j
        End With
    End If
End If
Next i
End Sub

Sorry going back to this i finally got why it is xltoleft. I have three columns, A B and D that i leave blank just to have space in between some of the items there, putting random variables there helped
however would like to keep those empty, any way to work around that with this code?

 

[offthelip]

The code I have written doesn't do anything to columns A, B and D, it only writes into columns 6 onwards i.e F onwards

 

[drop05]

copy that, i see. O

The code I have written doesn't do anything to columns A, B and D, it only writes into columns 6 onwards i.e F onwards

oh okay i am seeing it now that i am testing it more. I have to be on sheet 1 when running the code, when i went to sheet two and ran the code it got different values. Is there a way where I dont have to be on sheet 1 to get the values i want from sheet 1 because what i am thinking of doing is using a third sheet to do this?

 

[drop05]

The code I have written doesn't do anything to columns A, B and D, it only writes into columns 6 onwards i.e F oncoy that i

The code I have written doesn't do anything to columns A, B and D, it only writes into columns 6 onwards i.e F onwards

Copy that i see, oh okay i am seeing it now that i am testing it more. I have to be on sheet 1 when running the code, when i went to sheet two and ran the code it got different values. Is there a way where I dont have to be on sheet 1 to get the values i want from sheet 1 because what i am thinking of doing is using a third sheet to do this?

Copy that i see, oh okay i am seeing it now that i am testing it more. I have to be on sheet 1 when running the code, when i went to sheet two and ran the code it got different values. Is there a way where I dont have to be on sheet 1 to get the values i want from sheet 1 because what i am thinking of doing is using a third sheet to do this?