Change code from Function to sub?

realniceguy5000

realniceguy5000

Board Regular
Joined
Aug 19, 2008
Messages
148
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

Code: 
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

Code: 
=re(C5,F5)
 
Sort by date Sort by votes
If you are going to put formulas in the cells, why not just a formula that accomodates both situations. I'm assuming your numbers are stored as text because you are looking for a starting value of 01.

Code: 
Sub test()
Range("G5:G5000").Formula = "=IF(LEFT(C5,2)=""01"",F5*12,IF(LEFT(C5,2)=""03"",F5*24,""""))"
End Sub

or if you don't want the formula left in the cell, just the result:


Code: 
Sub test2()
With Range("G5:G5000")
    .Formula = "=IF(LEFT(C5,2)=""01"",F5*12,IF(LEFT(C5,2)=""03"",F5*24,""""))"
    .Value = .Value
End With
End Sub
 
Upvote 0
Thanks for the quick reply ...That wasn't so complicated as I thought.

It works I suppose but just not the way I thought it would here is the problem I'm having...

since I have formula's in other cells waiting for this data I get a bunch of #value! in all the cells that dont have data in them yet. So I need a way for the formula not to be placed in the cell G till a number is placed in F. Only F has a number and the case is correct in C then it needs to place your formula in?

Any other idea's

Thank You,Mike
 
Upvote 0
Hi, I think I would rather the formula not be in the cell unless there is data in the other col. as described above.

I suppose I only have two options?

Yes I want the formula but I dont want the name errors.
No Formula in the cell and no name errors.

So I'm still thinking No...

But yes once data is in the other cells the formula will be left alone?

Thank You,Mike
 
Upvote 0
The reason I'm asking is we can change the formula to account for there being no data in Column F and then we can just put the formula there.

If you want it to put the formula in when data is actually put into column F, it will have to be done another way.
 
Upvote 0
HOTPEPPER said:
The reason I'm asking is we can change the formula to account for there being no data in Column F and then we can just put the formula there.

If you want it to put the formula in when data is actually put into column F, it will have to be done another way.
Click to expand...

Yes I think that would work the best. The part where data is put into F then the formula will show up?

Thanks again for your support!!!

Mike
 
Last edited:
Upvote 0
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.


Code: 
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.
 
Upvote 0
HOTPEPPER said:
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.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, x As Long
[COLOR=red]Set d = Intersect(Target, Range("F5:F5000"))[/COLOR]
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.
Click to expand...

Thank You , But I am getting an object required error in red above?

I tried to change the code to this
Code: 
[COLOR=#ff0000]Set d =  Range("F5:F5000")[/COLOR]
However I'm just guessing but that changed I made. Caused the script to put the formula in all the cells again so I get those Value! errors?

I think were getting closer, I will keep playing around with it maybe I can get lucky...

Thanks again for your help, Mike
 
Upvote 0
Ok I got this to almost work now. I know it looks like a mess but seems to working correctly.

Except How can I keep the formula's from being created at all if there is nothing in those rows yet? Or can I just call another proceedure to delete those cells based on the entry in another?

I'll keep working... Thank You...

Code: 
Sub Check() 'Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, x As Long
Dim cc As Range, dd As Range, ee As Range
Set cc = Range("c5:c1200")
Set dd = Range("d5:d1200")
Set ee = Range("e5:e1200")
Set d = Range("f5:f1200") 'Intersect(Target, Range("F5:F1200"))
If cc Is Nothing Then GoTo 10
If dd Is Nothing Then GoTo 10
If ee Is Nothing Then GoTo 10
If d Is Nothing Then GoTo 10
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
10    Calculate
    
Application.EnableEvents = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
520
Big_Al_fae_the_rigs
B
JenniferMurphy
  • Solved
Interesting "trick" to get around a table problem
Replies
3
Views
274
Alex Blakenburg
A
I
  • Solved
In Excell VBA the set variable change without clear reason
Replies
5
Views
167
Micron
M
FatalLordes
  • Question Question
VBA works on step through but not when run normally?
Replies
2
Views
123
Logit
Logit
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
317
kevin9999
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top