Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 2nd, 2011, 04:35 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default creating a Left Function using a macro

Hi

I need to create a Left forumla which is created within a macro and placed in cell I2 and autofilled to the variable number z + 1.

The left forumla needs to add together the value in cell J2 and the value K2. However i need to cut the value in J2 so the result is only 7 characters long.

I think its should look like the following but I think this take the character off the end of the string instead of the first.

Code:
PPCWBSht.Range("J2").forumla = "=Left(J2 & K2,7)"Can anyone help me?
Thanks

Jeskit :-)

Last edited by jeskit; June 2nd, 2011 at 05:31 AM..
 
Old June 2nd, 2011, 07:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Hi there..

if you need a result that is only seven char long, and you have to cut J, then you will have to count the chars in K, then substract that to seven, and reduce J to that diference... IT's clear now?
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old June 3rd, 2011, 08:57 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Thumbs up

Hi

Thanks for replying, i get it i'll try to create a forumla using that logic!

Thanks for your help. Grealty appricated
 
Old June 6th, 2011, 05:54 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

If you are applying formula it will become circular reference (as you are calculating J2 based on J2 itself) and you need to handle it.

Alternatively you can try that in VBA

Code:
Sub DD1()
 For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
    Range("D" & i1).Value = Left(Range("D" & i1).Value & Range("E" & i1).Value, 7)
    Next i1
 
End Sub
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Some explanation about left function firoz.raj Beginning VB 6 1 August 9th, 2009 02:21 AM
Undefined function 'Left' in expression Morris Access 13 July 23rd, 2008 02:07 AM
Undefined function 'Left' in expression MountainProud Access 4 October 23rd, 2006 10:48 AM
retreive function/Line from macro or function? MikoMax J2EE 0 April 1st, 2004 04:42 AM
Creating Access macro using C# pankaj_daga ADO.NET 1 March 30th, 2004 01:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.