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!

 

[drop05]

yes very simple just add another loop index like this:

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

you can see the advantages of using number to address row and columns because you can easily calculate row and column numbers. So in VBA I very rarely using letter s to address columns numbers work better in vba

Having a bit of trouble understanding the logic here on how this combined the example of the three separate subs(). If you do not mind explaining code a bit please and thank you!

 

[offthelip]

Noticed how its the same code just changed the 52 to 53 and the 163 to 164 in the second half of the code and so on

As you have already spotted in the name routine you address row 52 for the input and row 163 for the output
in the address routine it is rows 53 and 164, and the ID routine it is 54 and 165
So I just created a loop controlled by the variable kkk that loops three times with the value of kkk being 0 ,1 and 2
This number is added to the row number given in the name routine (52 and 163) to give the row numbers required for the other two routines

For kkk = 0 To 2

........

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

this line picks up 52 the first time when kkk = 0 ( assuming i=0)
it picks up 53 the second time (kkk=1)
and 54 the third time (kkk=2)
when i = 1 it picks up
403 the first time
404 the second time
405 the third time

 

[drop05]

I can't think of an easy solution to changing blank to X, unless you want to limit it to specific addresses. The code does what you asked for!!!

I am not sure what you think is a problem here, you can do 100 iterations just by changing the i loop. It might take slightly longer but there is no reason why it should fail

As you have already spotted in the name routine you address row 52 for the input and row 163 for the output
in the address routine it is rows 53 and 164, and the ID routine it is 54 and 165
So I just created a loop controlled by the variable kkk that loops three times with the value of kkk being 0 ,1 and 2
This number is added to the row number given in the name routine (52 and 163) to give the row numbers required for the other two routines

For kkk = 0 To 2

........

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

this line picks up 52 the first time when kkk = 0 ( assuming i=0)
it picks up 53 the second time (kkk=1)
and 54 the third time (kkk=2)
when i = 1 it picks up
403 the first time
404 the second time
405 the third time

Okay i think im getting it, so if I = 0 then it gets only the first iteration of 52, 53, 54
If i = 1 then it does 52, 53, 54 & jumps down 351 rows and then gets the values from 403, 404 and 405
if i = 2 then jumps another 351 and gets 754 755 756?
Correct me if im wrong please

 

[offthelip]

The code when I last saw it only did a loop of 2 withthe i loop so i has value 0 and 1, so it never gets to 754, 755 756, but it would if you change the i loop to:

for i = 0 to 2

also be careful about the order of the loops, I have put the kkk loop OUTSIDE the i loop, so the first time through kkk = 0 and i = 0, the next time kkk=0 i=1 then kkk=1 and i = 0 then kkk=1 i =1 etc
so the order the cells are refereced in is 52 then 403 then 53 then 404 the 54 then 405

 

[drop05]

The code when I last saw it only did a loop of 2 withthe i loop so i has value 0 and 1, so it never gets to 754, 755 756, but it would if you change the i loop to:

for i = 0 to 2

copy that, sorry should have worded it like that, i worded it as
if I = 0 does 52,53,54
If i = 1 does 403,404,405
if i = 2 does 754,755,756

but same thing as saying, (as i meant to say how you did)

if I = 0 to 0 52,53,54
If i = 0 to 1 403,404,405
if i = 0 to 2 754, 755,756

am i understanding the logic correctly?

 

[offthelip]

I don't know whether you have understood the logic correctly, what you have stated is incomplete. To help you understand looping and using calculated index values, open a blank workbook and put these two subroutines in it, then run the first one on sheet1 and look at the results it should show you how the indices tie up with the addresses. Then run the second one on sheet2.

Sub demo()
For i = 1 To 3
 For j = 1 To 5
  Cells(i, j) = "i=" & i & "j=" & j
 Next j
Next i

End Sub
Sub demo2()
For kkk = 0 To 2
For i = 1 To 3
 For j = 1 To 5
  Cells(5 * kkk + i, j) = "i=" & i & "j=" & j & "KKK=" & kkk
 Next j
Next i
Next kkk

End Sub

 

[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 onwards

Hello again, i want to say thank you for all the help you have provided! I have put it all together and coming out great!.
I have one questions. I put an area where a user has to put a X number of times. That number is the number of entries. With this code:

Sub NameAdd()

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

.Range(.Cells(k + 163 + jj * 11, 6 + i), .Cells(k + 163 + jj * 11, 6 + i)) = inarr(1, j)
Next j
End With
End If
End If
Next k
Next i

End Sub

is there a way to work in that number with the for I = 0 to 1 part of the code
X is located in this workbook on sheet called "Path" on cell D10, maybe setting it a variant called num_ent and using that in the for loop, For I= 0 to num_ent
If im thinking of it right if not is there a way to work it in?

thank you!

 

[offthelip]

X is located in this workbook on sheet called "Path" on cell D10, maybe setting it a variant called num_ent and using that in the for loop, For I= 0 to num_ent
If im thinking of it right if not is there a way to work it in?

This is exactly the way to do it, although to be really pedantic, the description should be "setting the variant called num_ent to cells D10 on worksheet "path"
i.e

num_ent = Worksheets("path").Range("D10")

 

[drop05]

This is exactly the way to do it, although to be really pedantic, the description should be "setting the variant called num_ent to cells D10 on worksheet "path"
i.e

num_ent = Worksheets("path").Range("D10")

Awesome, and yes i did add that in as well and it works great, think that is easier than having to type a number, i can have a user enter a number and then that number is used through the code!

 

[drop05]

This is exactly the way to do it, although to be really pedantic, the description should be "setting the variant called num_ent to cells D10 on worksheet "path"
i.e

num_ent = Worksheets("path").Range("D10")

you’ve helped me with this code. I have a question in a new thread, most recent one. Using some what of the same logic. But that one I am really confused on. If you have any ideas on it. Using the same format of sheet 1 and 2 just different logic. If not totally understand. Had a rearrange in this work is all and implemented someone else so it’s not left to right reading the data.
I can tag you in it, would appreciate the assistance of getting starting point!