Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 21st 19, 04:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,058
Default Code need to be changed

On Friday, 16 August 2019 17:16:37 UTC+2, GS wrote:
On Thursday, 15 August 2019 21:50:21 UTC+2, GS wrote:
Try again *after* fixing your sheet name:
change "Prod.Ticket'!"
to "'Prod.Ticket'!"
so it *includes* the leading apostrophe!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,
unfortunately, it doesn't work. When I insert a leading apostrophe, VBA
asks me to debug and the code stopped at the first line where it generates
the formula: Range("BT7").Formula = "=" & myPath & wbName &
"'Prod.Ticket'!BT7"

I want to clarify that the code works but when I run a macro, which
increases the product no. in A16 by 1 (e.g. from 10120 to 10121), the code
does not trigger somehow and the values stay the same as for the previous
product no. (10120). But when I put 10121 directly into A16 and hit enter,
the code triggers and changes all values to the ones of 10121. I think
that if I have the product no. increased by 1, through a macro, the code
doesn't recognise the change. It seems to miss the hit on the enter
button.


My next suggestion is going to be to remove the period in your sheetname
because I have found that adding punctuation/space characters to sheetnames
causes coding problems when sheetnames have to be used. I just follow the
same naming rules as for Defined Names and code always works as expected...

Range("BT7").Formula = "=" & myPath & wbName & "ProdTicket!BT7"

..because the apostrophes aren't required!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Hi Garry,
sorry for only replying now. I managed to change my code so it would do what
I wanted. See below the code which I came up with. As I said it might be
awkward but I've never really learned VBA.

I named cell A16 as "CODE". Range A16:W24 is merged. Cell GG1 is just an
empty cell I use to temporarily store the next CODE number to be used.

Range("GG1").Select
ActiveCell.FormulaR1C1 = "=CODE+1"
Range("A16:W24").Select
Selection.UnMerge
Range("GG1").Select
Selection.Copy
Range("A16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _ :=False, Transpose:=False
Range("A16:W24").Select
Selection.Merge
Range("GG1").Select
Selection.ClearContents
Range("A16:W24").Select

After this piece of code there is all my other code wich populates my form
with values of an Excel file which is called <CODE+1.xls (e.g.: 10121.xls)

Ok, that is just to say thank you for your help and in case you are
interested how I am doing.

Cheers


Glad to hear you got it sorted out! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
do some code after the sheet name is changed ARHangel Excel Discussion (Misc queries) 1 January 18th 08 10:39 AM
Run code only when a sheet has not been changed Harry F. Excel Programming 4 October 5th 07 02:35 PM
How can this code be changed Greg B Excel Discussion (Misc queries) 3 May 18th 05 04:32 PM
option buttons run Click code when value is changed via VBA code neonangel Excel Programming 5 July 27th 04 08:32 AM
Code - if T changed, change date in W to date it is changed Sandy[_3_] Excel Programming 2 July 27th 03 05:33 PM


All times are GMT +1. The time now is 02:00 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017