Searching for values in a given range and then prepending a formula
 

Hello

I'd trying to write some code to do the following. Any help much
appreciated.

Find the last row in the worksheet with cells that are not empty, then
define a selection between that row and row 4.
Within that selection, iteratively find cells that contain a value
(number only). For each cell that does, take that number and write
into a simple formula in that cell: '=[found value]/$H$3'.

Thanks
Ben

Searching for values in a given range and then prepending a formul
 

for a specific column I assume. For illustration, for the pseudo code below,
I will use column B.

Sub ABC()
Dim rng as Range, rng1 as Range, cell as Range
set rng = Range(cells(4,2),cells(rows.count,2))
on error resume next
set rng1 = rng.specialcells(xlconstants,xlnumbers)
On error goto 0
if not rng1 is nothing then
for each cell in rng1
cell.formula = "=" & cell.Value & "/$H$3"
next
End if
End sub

--
Regards,
Tom Ogilvy


"Ben Burns" wrote:

Hello

I'd trying to write some code to do the following. Any help much
appreciated.

Find the last row in the worksheet with cells that are not empty, then
define a selection between that row and row 4.
Within that selection, iteratively find cells that contain a value
(number only). For each cell that does, take that number and write
into a simple formula in that cell: '=[found value]/$H$3'.

Thanks
Ben

Searching for values in a given range and then prepending a formul
 

On 18 Jun, 13:38, Tom Ogilvy
wrote:
for a specific column I assume. For illustration, for the pseudo code below,
I will use column B.

Sub ABC()
Dim rng as Range, rng1 as Range, cell as Range
set rng = Range(cells(4,2),cells(rows.count,2))
on error resume next
set rng1 = rng.specialcells(xlconstants,xlnumbers)
On error goto 0
if not rng1 is nothing then
for each cell in rng1
cell.formula = "=" & cell.Value & "/$H$3"
next
End if
End sub

--
Regards,
Tom Ogilvy

"Ben Burns" wrote:
Hello

I'd trying to write some code to do the following. Any help much
appreciated.

Find the last row in the worksheet with cells that are not empty, then
define a selection between that row and row 4.
Within that selection, iteratively find cells that contain a value
(number only). For each cell that does, take that number and write
into a simple formula in that cell: '=[found value]/$H$3'.

Thanks
Ben

Brilliant. Thank you Tom