Cell Reference Location
My program distributes data from different sources. My current problem is
that the location the data is sent (i.e. cells(1,5) changes depending on other data that is input. For example, if someone types a paragraph in a text box, the cells below it move so they can still be seen. Later if they delete the paragraph and replace it with one sentence, the cells below are shifted back up. The way to deal with this that came to mind was to have a variable in each cell name. (i.e. instead of cells(1,5) it would be cells(1+x,5+y)) Is this the best way to do it, or their an easier way. Can I "lock" a cell so that they data is always sent to and read from there even if it's location changes later? Thanks. |
Cell Reference Location
Thanks,
I think what I'm going to go with right now is a new function I learned about this afternoon. I name the location of the cells once and give it a variable name. For Example: Public LCTireDescLoc As Range Sub NamingPublicVariables() Set LCTireDescLoc = Sheets("Load & Calc").Range("D10:H10") End Sub Now, anytime I change the location of the LCTireDescLoc, I only have to change it once. Everywhere else only knows the location by the variable name. Now, if I can only get the location to change using a variable, Instead of manually changing it, I will be in better shape... "CLR" wrote: I use these lines to paste a Signature Box at the end of unknown length data.......perhaps it might shine some light. ActiveSheet.Shapes("Rectangle 6").Select 'this is the origin of the SigBox Selection.Copy Range("A8").Select Selection.End(xlDown).Select Selection.Offset(3, 8).Select ActiveSheet.PasteSpecial Format:="MS Office Drawing Object", Link:=False, _ DisplayAsIcon:=False Selection.Name = "SigBox" hth Vaya con Dios, Chuck, CABGx3 "CWillis" wrote: My program distributes data from different sources. My current problem is that the location the data is sent (i.e. cells(1,5) changes depending on other data that is input. For example, if someone types a paragraph in a text box, the cells below it move so they can still be seen. Later if they delete the paragraph and replace it with one sentence, the cells below are shifted back up. The way to deal with this that came to mind was to have a variable in each cell name. (i.e. instead of cells(1,5) it would be cells(1+x,5+y)) Is this the best way to do it, or their an easier way. Can I "lock" a cell so that they data is always sent to and read from there even if it's location changes later? Thanks. |
Cell Reference Location
I use these lines to paste a Signature Box at the end of unknown length
data.......perhaps it might shine some light. ActiveSheet.Shapes("Rectangle 6").Select 'this is the origin of the SigBox Selection.Copy Range("A8").Select Selection.End(xlDown).Select Selection.Offset(3, 8).Select ActiveSheet.PasteSpecial Format:="MS Office Drawing Object", Link:=False, _ DisplayAsIcon:=False Selection.Name = "SigBox" hth Vaya con Dios, Chuck, CABGx3 "CWillis" wrote: My program distributes data from different sources. My current problem is that the location the data is sent (i.e. cells(1,5) changes depending on other data that is input. For example, if someone types a paragraph in a text box, the cells below it move so they can still be seen. Later if they delete the paragraph and replace it with one sentence, the cells below are shifted back up. The way to deal with this that came to mind was to have a variable in each cell name. (i.e. instead of cells(1,5) it would be cells(1+x,5+y)) Is this the best way to do it, or their an easier way. Can I "lock" a cell so that they data is always sent to and read from there even if it's location changes later? Thanks. |
All times are GMT +1. The time now is 07:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com