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

drop05

Board Regular
Joined
Mar 23, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,906
Office Version
  1. 2010
Platform
  1. Windows
try this code:
VBA 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

Board Regular
Joined
Mar 23, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows
try this code:
VBA 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

Well-known Member
Joined
Dec 23, 2017
Messages
1,906
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
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

Board Regular
Joined
Mar 23, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
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:
VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,906
Office Version
  1. 2010
Platform
  1. Windows
the code should work for two iterations, the line at the start:
VBA Code:
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:
VBA Code:
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 :
VBA Code:
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
VBA Code:
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:
VBA Code:
.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
VBA Code:
For i = 0 To 2
to get 3 iterations ( 0,1,2)
 

drop05

Board Regular
Joined
Mar 23, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,906
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Mar 23, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 23, 2021
Messages
111
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,773
Messages
5,677,640
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top