Looping Question
 

Hello, I'm having difficulty writing a macro to enter the following formula
in cell G6:

=MID(H6,FIND(",",H6,FIND(",",H6)+1)+1,3)

and continue to add the same formula in subsequent rows of column G, until
there is no longer any values in column H.

Any and All Help Is Appreciated - Thank You

Looping Question
 

Hi,
Try the following (just review the 'CHANGE HERE' section):
'------------------------------------------------------------
Sub test()
Dim rgData As Range
Dim DestRange As Range
Dim DestCol As String
Dim f As String 'formula

'---- CHANGE HERE -----
Set rgData = ActiveSheet.Range("H6") 'first cell of data in H
DestCol = "G" 'where to write the formulas: column letter
f = "=MID(H6,FIND("","",H6,FIND("","",H6)+1)+1,3)" 'formula for 1st cell
'----------------------

'range of data in H
Set rgData = Range(rgData, rgData.EntireColumn.Cells(65536).End(xlUp))
'col G
Set DestRange = Range(DestCol & ":" & DestCol)
'G cells matching rgData
Set DestRange = Application.Intersect(DestRange, rgData.EntireRow)
'Apply formula in one single shot: reference updates
automatically(H6,H7,...)
DestRange.Formula = f
End Sub
'------------------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"MWS" wrote:

Hello, I'm having difficulty writing a macro to enter the following formula
in cell G6:

=MID(H6,FIND(",",H6,FIND(",",H6)+1)+1,3)

and continue to add the same formula in subsequent rows of column G, until
there is no longer any values in column H.

Any and All Help Is Appreciated - Thank You

Looping Question
 

Thank You - It works perfectly and I Appreciate Your Assistance!!!!

"sebastienm" wrote:

Hi,
Try the following (just review the 'CHANGE HERE' section):
'------------------------------------------------------------
Sub test()
Dim rgData As Range
Dim DestRange As Range
Dim DestCol As String
Dim f As String 'formula

'---- CHANGE HERE -----
Set rgData = ActiveSheet.Range("H6") 'first cell of data in H
DestCol = "G" 'where to write the formulas: column letter
f = "=MID(H6,FIND("","",H6,FIND("","",H6)+1)+1,3)" 'formula for 1st cell
'----------------------

'range of data in H
Set rgData = Range(rgData, rgData.EntireColumn.Cells(65536).End(xlUp))
'col G
Set DestRange = Range(DestCol & ":" & DestCol)
'G cells matching rgData
Set DestRange = Application.Intersect(DestRange, rgData.EntireRow)
'Apply formula in one single shot: reference updates
automatically(H6,H7,...)
DestRange.Formula = f
End Sub
'------------------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"MWS" wrote:

Hello, I'm having difficulty writing a macro to enter the following formula
in cell G6:

=MID(H6,FIND(",",H6,FIND(",",H6)+1)+1,3)

and continue to add the same formula in subsequent rows of column G, until
there is no longer any values in column H.

Any and All Help Is Appreciated - Thank You

Looping Question
 

Sub test()
Dim iLastRow As Long
Dim sFormula As String

sFormula = "=MID(H6,FIND("","",H6,FIND("","",H6)+1)+1,3)"
iLastRow = Cells(Rows.Count, "H").End(xlUp).Row
With Range("G6")
.Formula = sFormula
.AutoFill .Resize(iLastRow - 5)
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MWS" wrote in message
...
Hello, I'm having difficulty writing a macro to enter the following
formula
in cell G6:

=MID(H6,FIND(",",H6,FIND(",",H6)+1)+1,3)

and continue to add the same formula in subsequent rows of column G, until
there is no longer any values in column H.

Any and All Help Is Appreciated - Thank You