Repeat Cell until blank, then use the cell directly under blank and start repeating that cell.

Johnny Thunder · Apr 9, 2010

Hello,

I have a spreadsheet that has several pieces of data that is next to a column with totals Column H has one name then multiple codes under it, I only need the first name in cell H3. In Column I cell I3 is the amounts I need.

What I am trying to achieve is placing a formula in the column before H and have it say (Repeat cell H3 until Blank, skip one cell and then return the new name under blank and fill that down and so on and so fourth until data is complete.

I have attached a sample of the spreadsheet, please see Highlighted cells for Question. Thank you in advance.
file:///C:/Documents and Settings/JLee/Desktop/Repeat Cell Until New Cell Appears.xls

swinglow · Apr 9, 2010

your attachment didn't work

Johnny Thunder · Apr 9, 2010

How do you attach a xls. in this thread? I dragged the file into the message box? Sorry about that.

SamTYler · Apr 9, 2010

Your attachment link point to your harddrive...On your computor, on our computors it points to our harddrives.

Code:

Sub RepeatH3()
Dim Last Row As Long
LastRow = Cells(Rows.Count, 8).End(xlUP).Row
Dim R as Long
Dim Rh As Long
Rh = 3
Dim HName As String
 
For R = 3 To LastRow
   HName = Cells(Rh, 8).Value
   If Cells(R, 7).Offset(0,1).Value <> ""
      Cells(R, 7).Value = HName
      Next R
   Else
      Rh = R + 1
   End If
Next R
End Sub

Johnny Thunder · Apr 9, 2010

I'm sorry, i am not sure what to do with the code you just put in there? Can you give me instructions on what to do with it? Thanks!

SamTYler · Apr 9, 2010

Whatever sheet it's supposed to wotk on, copy and paste it into that sheets' codepage in VBA

From the sheet; press alt+F11

Insure the right codepage

Paste

Press F5

Check the sheet to see if it worked ok

Use on a copy of the workbook first just in case

Johnny Thunder · Apr 9, 2010

Here it is

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CJLee%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C07%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CJLee%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C07%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CJLee%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C07%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin;} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color

urple; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style>[FONT=&quot]https://secure.filesanywhere.com/v.asp?v=%89qe%8C%5Bcv%B4%A2%AB[/FONT]

SamTYler · Apr 9, 2010

Now that I know what columns you're talking about

Paste this in in the code page for the sheet you shared as "Sample", then press F5. USE A COPY OF THE WORKBOOK FIRST!

 Sub RepeatI6() Dim LastRow As Long LastRow = Cells(Rows.Count, 9).End(xlUp).Row Dim R As Long 'Working Row Dim Rj As Long 'Row with Name Rj = 6 Dim F As Long 'Working Column F = 6 Dim I As Long 'Column With Name I = 9 Dim J As Long 'Column With Values J = 10 Dim JValue As Currency 'easy way to get 2digit decimals For R = 6 To LastRow JValue = Cells(Rj, J).Value If Cells(R, I).Value <> "" Then Cells(R, F).Value = JValue Else 'R, J is blank Rj = R + 1 End If Next R End Sub

Johnny Thunder · Apr 9, 2010

Do you have this same macro in a formula that I can paste into a column?
Thank you in advance!

Johnny Thunder · Apr 9, 2010

Also,

The Macro is duplicating the Amounts (Column J) I just needed column I (Names) duplicated. Thanks!

Repeat Cell until blank, then use the cell directly under blank and start repeating that cell.

Johnny Thunder

Well-known Member

Excel Facts

swinglow

Active Member

Johnny Thunder

Well-known Member

SamTYler

Well-known Member

Johnny Thunder

Well-known Member

SamTYler

Well-known Member

Johnny Thunder

Well-known Member

SamTYler

Well-known Member

Johnny Thunder

Well-known Member

Johnny Thunder

Well-known Member

Similar threads

Forum statistics

Share this page

Repeat Cell until blank, then use the cell directly under blank and start repeating that cell.

Well-known Member

Excel Facts

Active Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock