Change code from Function to sub?

[realniceguy5000]

Hi I posted this at another site but havent got to many bites so I thought I would try here.

I would like some help converting this function into a sub. It will not work for some reason when I take the function out.

What I am trying to do is look at the first two digits of the number in column c (Range c5:c5000) if the number starts with 01 then I need the formula in column g = f5*12 if the number is 03 then I want the formula to be g=f5*24 I have several of these "cases" or "if" to put in can someone get me going a little further

Here is what someone gave me to work with but I'm having troubles getting it to work...I get a name error in the cell and I cant use this they way it is because the code needs to go into the sheet mod with a bunch of other code.Also when I use the Option Explicit the rest of my code wont work.

Thank You, Mike

Option Explicit
Function re(Cl As Range, Src As Range)
Dim i As Long
    Select Case Left(Cl, 2)
        Case "01": i = 12
        Case "03": i = 24
    End Select
   re = Src * i
End Function

=re(C5,F5)

 

[realniceguy5000]

Nevermind it isnt working after all. I got it all messed up now. going back to the your last post...I'll work at this tomorrow.

Thank You, Mike...

 

[realniceguy5000]

Still working at this: Can someone please advise:

I dont want the formula to show up in G if C,D,E,F are blank?

Thank You, Mike,

Here what I been trying to get to work...

Sub Check()
Dim C As Range
Dim D As Range
Dim E As Range
Dim F As Range
Dim G As Range
Set C = Range("C5:C25")
Set D = Range("D5:D25")
Set E = Range("E5:E25")
Set F = Range("F5:F25")
Set G = Range("G5:G25")
    For Each C In Range("C5:C25")
        If C Is Nothing Then
            G.ClearContents
        Else: GoTo 10
        End If
    Next
    
    
    
10  For Each D In Range("D5:D25")
        If D Is Nothing Then
            G.ClearContents
        Else: GoTo 20
        End If
    Next
    
    
20  For Each E In Range("E5:E25")
        If E Is Nothing Then
             G.ClearContents
        Else: GoTo 30
        End If
    Next
    
30 For Each F In Range("F5:F25")
        If F Is Nothing Then
            G.ClearContents
        Else
        G.Formula = "=IF(LEFT(C5,2)=""01"",F5*12,IF(LEFT(C5,2)=""03"",F5*24,""""))"
        End If
   Next
  
       
End Sub

 

[Scott Huish]

My code should work as written. But why are you using GoTo and line numbers?

Also, the code as you have it written will not automatically add or delete formulas as data is entered.

What version of Excel are you using?

 

[realniceguy5000]

My code should work as written. But why are you using GoTo and line numbers?

Also, the code as you have it written will not automatically add or delete formulas as data is entered.

What version of Excel are you using?

Hi Again. I'm using excel 2002,

I don't know why I'm using goto except it was the only way for me to make things work some what. Your code did work in a way.However I cant seem to get rid of the formula in G when there is nothing in the other column cells. So that is why I was trying to edit it.

I'm sorry I am butchering your code...

Mike

 

[realniceguy5000]

I wasn't sure if you wanted to delete the formula if you deleted the value in Column F, so it doesn't do that as written.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, x As Long
Set d = Intersect(Target, Range("F5:F5000"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        x = c.Row
        If IsNumeric(c) Then c.Offset(, 1).Formula = "=IF(LEFT(C" & x & ",2)=""01"",F" & x & "*12,IF(LEFT(C" & x & ",2)=""03"",F" & x & "*24,""""))"
    Next
Application.EnableEvents = True
End Sub

Copy the code above.
Right click on the sheet tab where you want this to happen on.
Click on View Code.
Paste into white area.
Hit Alt-q

The code will be saved with the workbook when you save the workbook.

Let me restart again...

Ok I went back to this code: However I get an object required at:

Set d = Intersect(Target, Range("F5:F5000"))

Also I really want the formula not to show up if F is blank.
Thank You, Mike

 

[realniceguy5000]

Ok, I finally got it with some help from a friend on another site. Here is the end result.

Thanks to Hotpepper and Shg...

Sub try()
Application.EnableEvents = False
Dim iRow As Long
 
 
    For iRow = 5 To 25
 
        If Not IsEmpty(Cells(iRow, "F")) Then
        Cells(iRow, "G").FormulaR1C1 = "=IF(LEFT(RC3,2)=""01"",RC6*12,IF(LEFT(RC3,2)=""03"",RC6*24,""""))"
 
 
            Else: Cells(iRow, "G").ClearContents
        End If
    Next iRow
    Application.EnableEvents = True
 
End Sub