UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert One Row If, Office 2010    
 
   
Brepea
post Jul 17 2017, 07:12 AM
Post#1



Posts: 364
Joined: 11-January 09
From: UK


Hi All

I have one column which stipulates whether a row on the next line needs to be inserted for e.g.

Col A | Col B | Col C
------------------------------
" " | Name | Surname
Yes | Name 2 | Surname 2
" " | Name 3 | Surname 3

...and so on. So i want to be able to say for each row in the range A2:C1000, if Col A = "yes" insert row beneath it (on next line).

Is this possible?

Thanks
Go to the top of the page
 
River59
post Jul 17 2017, 07:39 AM
Post#2



Posts: 1,181
Joined: 7-April 10
From: Detroit, MI


Yes, it's possible. You can use this to see how to reference and loop through the rows. Pass your own variables.
This refers to the row, and column number (Col A)

For i = 1 To 20

If Cells(i, 1) = "Yes" Then
Rows(i + 1).Select
Selection.Insert Shift:=xlDown
End If
Next

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
Brepea
post Jul 17 2017, 08:02 AM
Post#3



Posts: 364
Joined: 11-January 09
From: UK


Hi River - yes that works well thanks.

Now with that new row inserted - how do i add values to various cells in that new row (for each new row there will be different values)...?

Thanks
Go to the top of the page
 
Brepea
post Jul 17 2017, 08:31 AM
Post#4



Posts: 364
Joined: 11-January 09
From: UK


Ok - i think i worked it out i've added this line below the insert:

Cells(i + 1, 1).Value = AreaAveName & " AVE"


and it works...
Go to the top of the page
 
River59
post Jul 17 2017, 08:41 AM
Post#5



Posts: 1,181
Joined: 7-April 10
From: Detroit, MI


I will get you started on this but then you have to try to understand what this code is doing to make it work in your situation. There are many ways to do this (I went to Google) so you need to find one that you are comfortable with. You can use the offset property in Excel to advance the columns rather than advancing the cell selected as I show here.

For i = 1 To 20

If Cells(i, 1) = "Yes" Then
Rows(i + 1).Select
Selection.Insert Shift:=xlDown
Cells(i + 1, 1).Select
ActiveCell.Formula = "NewInfoA"
Cells(i + 1, 2).Select
ActiveCell.Formula = "NewInfoB"

End If
Next

Have fun with your project!

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
River59
post Jul 17 2017, 08:44 AM
Post#6



Posts: 1,181
Joined: 7-April 10
From: Detroit, MI


That's much cleaner than what I sent. Well done ... keep at it and it will come together.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th July 2017 - 03:32 AM