0

I would like to put the below coding into a vba like a function. There is a bunch of data created already by VBA, and when the VBA does its work, then the following function should be run, but i dont know how to add to my vba so that the function always runs as long as data contains. The macro i created already puts the datasheet together, now instead of creating the below with lenthy codings, i just want my macro to run the below, like a man who clicks on the below right hand corner of the cell which contains the below function. It should be something: Activesheet.ForulaR1C1 = "=RIGHT(AY4,LEN(AY4)-FIND(".",AY4))" something. Can someone help me? Thanks

ORIGINAL FUNCTION TO BE RUN "=RIGHT(AY4,LEN(AY4)-FIND(".",AY4))"

This is where I am at now:

Sub Project_numbers()

Dim j As Integer

Zorro = Range("AY" & Rows.Count).End(xlUp).Row
o = 4


Worksheets("MJE").Range("AF" & o).FormulaR1C1 = "=RIGHT(AE4,LEN(AE4)-FIND(".",AE4))"
o = o + 1

End Sub
Improve this question
4
  • Can you put more of your code up? Commented Dec 20, 2016 at 10:53
  • So basically this code cuts everything down which is designated after the first dot in a string. Like "1123456.12.12" and from this string it returns 1123456 without adding ".12.12" at the end. Commented Dec 20, 2016 at 11:08
  • What happens when you put "1123456.12.12" into AY4 and the formula "=RIGHT(AY4,LEN(AY4)-FIND(".",AY4))" in another cell? Commented Dec 20, 2016 at 12:16
  • Oh sorry, I told you in a wrong way. It gives back the ending part which is after the dot. Commented Dec 20, 2016 at 13:37

1 Answer 1

Reset to default
1

You have a couple of problems here. The biggest is that you've got quotation marks in your formula. VBA reads these as the end of the string, so it's interpreting your formula as two separate text strings: =Right(AE4,LEN(AE4)-FIND( and ,AE4)), separated by a .. This isn't a structure VBA can do anything with, so it's going to fail at that point.

When you're inserting a formula with VBA that contains quotation marks, you need to use two quotes together to indicate that it's a literal quote mark that's part of the string, rather than the end of the string:

"=RIGHT(AE4,LEN(AE4)-FIND(""."",AE4))"

The second problem is that you're using the FormulaR1C1 method, which expects cell references to be given in R1C1 (row#column#) notation, rather than A1 notation, but then passing it a formula that uses A1 notation. Again, this is going to confuse the issue and produce errors.

I'm guessing you used the macro recorder to get the syntax, then inserted your own formula? The macro recorder, for some weird reason, loves to use the R1C1 reference style, but we can use a different method for written code.

The full line you need is:

Worksheets("MJE").Range("AF" & o).Formula = "=RIGHT(AE4,LEN(AE4)-FIND(""."",AE4))"

EDITED TO ADD:

With further information, specifically that you need the range referenced to change as you loop, you have some options on how to do it.

1. Use the R1C1 reference style

This allows you to include relative references in formulae easily. You'll use R to designate the formula's row, and C to designate its column; so a cell that referred to itself would simply be =RC. You can follow the R and C with numbers to designate specific rows and columns, so cell B2 would be =R2C2 - row 2, column 2. More usefully, you can use =R[#]C[#] to offset your formula by a certain amount.

In your formula, assuming it's always going to be looking at column AE but whichever row the formula is entered into, your line would be:

Worksheets("MJE").Range("AF" & o).FormulaR1C1 = "=RIGHT(RC31,LEN(RC31)-Find(""."",RC31))"

2. Build your formula from variables.

You already have a variable you can use, o, so we can combine that with the rest of the string to get the appropriate references. It's harder to read, though...

Worksheets("MJE").Range("AF" & o).Formula = "=RIGHT(AE" & o & ",LEN(AE" & o & ") - FIND(""."",AE" & o & "))"

Personally, I find this method rather cumbersome to work with, but it's an option.

3. Assign the formula to your entire range as a single operation

Personally, I prefer this option; I find it to be the neatest one. I'm assuming, from your formula, that your data starts on row 4, and you want the formula to go into every cell between AE4 and the end of your data, which is stored in Zorro. You can use this line to add the formula in one go:

Worksheets("MJE").Range("AF4","AF" & Zorro).Formula = "=RIGHT(AE4,LEN(AE4)-FIND(""."",AE4))"

The cell references will update automatically for each row. There's no need for a loop with this method - of course, if you're looping anyway, that may be no great saving.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hello Werrf, what happens if I would like to use this formula for an unspecific range? I am trying to put together a kind of loop, which keeps repeating this formula until it finds no data any longer. Like A1-A10 holds data, and this functuion should be used in B1-B10, but the range may change each and every time.
Then you need to go back to the R1C1 formula, specific relative references. Something like "=RIGHT(RC[-1],LEN(RC[-1])-FIND(""."",RC[-1]))"
@T.Grof I've edited my answer with some options for you. Take a look and see if one works for you.
Hello Werrf, thanks for the answer! It was more than substantial!
Hello Werrf, thanks for the answer! It was more than substantial! Now my macro works! Thanks!! My second question, is there any book, that teaches me how create better macros, or how do you know so many ways to build up structures like this? Merry Christmas!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.