Welcome to the FTS Forum - Read the introduction here

Avatar
Please consider registering
Guest
Search
Forum Scope


Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Feed
Topic Rating: +5 Topic Rating: +5 (5 votes) 
sp_TopicIcon
Macros and Columns
Avatar
Lord Dio
Ultimate
Members
Forum Posts: 125
Member Since:
October 23, 2019
sp_UserOfflineSmall Offline
1
May 21, 2020 - 3:48 pm
sp_Permalink sp_Print
+1

Hi All,

I am fairly comfortable creating my macros and editing them if needs be, however a common issue I’m having is that when I decide to add new columns into my excel sheet for whatever reason, the macro doesn’t account for this and it runs its filters in the same column it did previously without shifting across. 

Is there a way to make my macros sticky to the columns I’ve applied them to so that this problem is eliminated? 

Thanks in advance!

Avatar
ArtTest
Moderator
Members

Moderators
Forum Posts: 292
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
2
May 21, 2020 - 4:29 pm
sp_Permalink sp_Print
+1

To be honest, not that I aware of in that way. You could name all your ranges, but that is a lot of work for minimal gain IMO.

The following users say thank you to ArtTest for this useful post:

Lord Dio
Avatar
Lord Dio
Ultimate
Members
Forum Posts: 125
Member Since:
October 23, 2019
sp_UserOfflineSmall Offline
3
May 21, 2020 - 9:03 pm
sp_Permalink sp_Print
+1

ArtTest said
To be honest, not that I aware of in that way. You could name all your ranges, but that is a lot of work for minimal gain IMO.

  

No worries thanks Art. I find that a really odd thing that Excel does this, but I am very much an amateur considering all its functionality! 

Avatar
leewillo75
Ultimate
Members
Forum Posts: 102
Member Since:
July 9, 2019
sp_UserOfflineSmall Offline
4
May 21, 2020 - 9:23 pm
sp_Permalink sp_Print
0

Understand your frustration as if it was a formula in a cell it would automatically update

Its a negative in VBA bit like USA dates but there are loads more plusses imo

Avatar
Beeble
Beginner
Members
Forum Posts: 50
Member Since:
July 31, 2019
sp_UserOfflineSmall Offline
5
May 21, 2020 - 10:04 pm
sp_Permalink sp_Print
+1

One thing you could do is define some global constants for each column in your VBA project and reference those in your code.

When you add a new column, you’d still have to update the global constants, but you’d only have to do it in one place. 

Avatar
Lord Dio
Ultimate
Members
Forum Posts: 125
Member Since:
October 23, 2019
sp_UserOfflineSmall Offline
6
May 22, 2020 - 12:33 pm
sp_Permalink sp_Print
0

Beeble said
One thing you could do is define some global constants for each column in your VBA project and reference those in your code.

When you add a new column, you’d still have to update the global constants, but you’d only have to do it in one place. 

  

Sorry mate not a clue, I use the “record” and “edit” macro buttons and thats the extent of my knowledge haha!

Avatar
Beeble
Beginner
Members
Forum Posts: 50
Member Since:
July 31, 2019
sp_UserOfflineSmall Offline
7
May 22, 2020 - 1:18 pm
sp_Permalink sp_Print
0

Ha ha, fair enough.

Time to learn a bit of VBA perhaps?

The following users say thank you to Beeble for this useful post:

Lord Dio
Avatar
ArtTest
Moderator
Members

Moderators
Forum Posts: 292
Member Since:
March 6, 2016
sp_UserOfflineSmall Offline
8
May 22, 2020 - 4:56 pm
sp_Permalink sp_Print
+1

look at the Chrissy spreadsheets course, probably on youTube, certainly available via his Twitter.

Avatar
@capitalchrisw
Norfolk, UK
Beginner
Members
Forum Posts: 100
Member Since:
September 11, 2019
sp_UserOfflineSmall Offline
9
May 22, 2020 - 6:49 pm
sp_Permalink sp_Print
0

Not sure Dio can access twitter now?

Avatar
Lord Dio
Ultimate
Members
Forum Posts: 125
Member Since:
October 23, 2019
sp_UserOfflineSmall Offline
10
May 22, 2020 - 10:38 pm
sp_Permalink sp_Print
0

@capitalchrisw said
Not sure Dio can access twitter now?

  

Good point!

Avatar
leewillo75
Ultimate
Members
Forum Posts: 102
Member Since:
July 9, 2019
sp_UserOfflineSmall Offline
11
May 23, 2020 - 3:07 am
sp_Permalink sp_Print
0

https://tigerspreadsheetsoluti…..30-for-30/

Thats the course …..

I was pratically a newbie when it came to VBA before lockdown. Learning this stuff has been a game changer for me

Long term lockdown will prob be the best thing that happened from a trading point of view becuase its allowed me to breath, reflect and improve

And without seeming to kiss ar*e Chrissy is a brilliant teacher

The following users say thank you to leewillo75 for this useful post:

Lord Dio, Dixon
Avatar
Matthew
Beginner
Members
Forum Posts: 14
Member Since:
February 23, 2019
sp_UserOfflineSmall Offline
12
May 23, 2020 - 6:52 pm
sp_Permalink sp_Print
0

I followed the 30 for 30 course myself got loads out of it I remember Chrissy mentioning he was going to do a course for analysing football data. Does anybody know when this will be happening?

Avatar
jb8426
Beginner
Members

KickStarter 2020
Forum Posts: 52
Member Since:
August 4, 2013
sp_UserOfflineSmall Offline
13
May 24, 2020 - 8:34 am
sp_Permalink sp_Print
0

yes concur about the 30/30 series started working through myself already benefiting after only a small number. Managed to create VBA macro to re-name the Football data direct sheets for record keeping purposes. A good time saver already, sure will be more re-finements can do as i continue through the course.

Avatar
becker
Beginner
Members
Forum Posts: 30
Member Since:
August 22, 2015
sp_UserOfflineSmall Offline
14
May 25, 2020 - 7:11 am
sp_Permalink sp_Print
0

Matthew said
I followed the 30 for 30 course myself got loads out of it I remember Chrissy mentioning he was going to do a course for analysing football data. Does anybody know when this will be happening?

  

I put my name forward for the course and Chrissy mailed me he will get back when the thing starts.

The following users say thank you to becker for this useful post:

Matthew
Avatar
johnpetrie86
Beginner
Members
Forum Posts: 21
Member Since:
January 31, 2019
sp_UserOfflineSmall Offline
15
May 26, 2020 - 12:33 pm
sp_Permalink sp_Print
0

Lord Dio said

@capitalchrisw said

Not sure Dio can access twitter now?

  

Good point!

  

What happened here? Did you get banned or something?

Avatar
Lord Dio
Ultimate
Members
Forum Posts: 125
Member Since:
October 23, 2019
sp_UserOfflineSmall Offline
16
May 26, 2020 - 3:41 pm
sp_Permalink sp_Print
0

johnpetrie86 said

Lord Dio said

@capitalchrisw said

Not sure Dio can access twitter now?

  

Good point!

  

What happened here? Did you get banned or something?

  

Yes. I don’t know why. They haven’t communicated anything to me. 

Avatar
chrissy_spreadsheets
Beginner
Members
Forum Posts: 5
Member Since:
November 1, 2019
sp_UserOfflineSmall Offline
17
June 18, 2020 - 1:00 pm
sp_Permalink sp_Print
0

Lord Dio said
Hi All,

I am fairly comfortable creating my macros and editing them if needs be, however a common issue I’m having is that when I decide to add new columns into my excel sheet for whatever reason, the macro doesn’t account for this and it runs its filters in the same column it did previously without shifting across. 

Is there a way to make my macros sticky to the columns I’ve applied them to so that this problem is eliminated? 

Thanks in advance!

  

Dio – I’m currently looking for topics for the next live series, we can cover this.  You could email me an example file if you like – info at tigersolutions dot co dot uk.

The football course is on the backburner until the end of lockdown.  I would say the 30 for 30 beginner course would be really useful even for those looking specifically at football data.

Avatar
Lord Dio
Ultimate
Members
Forum Posts: 125
Member Since:
October 23, 2019
sp_UserOfflineSmall Offline
18
June 19, 2020 - 7:04 am
sp_Permalink sp_Print
0

chrissy_spreadsheets said

Lord Dio said

Hi All,

I am fairly comfortable creating my macros and editing them if needs be, however a common issue I’m having is that when I decide to add new columns into my excel sheet for whatever reason, the macro doesn’t account for this and it runs its filters in the same column it did previously without shifting across. 

Is there a way to make my macros sticky to the columns I’ve applied them to so that this problem is eliminated? 

Thanks in advance!

  

Dio – I’m currently looking for topics for the next live series, we can cover this.  You could email me an example file if you like – info at tigersolutions dot co dot uk.

The football course is on the backburner until the end of lockdown.  I would say the 30 for 30 beginner course would be really useful even for those looking specifically at football data.

  

Thanks Chris. It’s far too large/messy/complicated a sheet to send over unfortunately. 

Avatar
chrissy_spreadsheets
Beginner
Members
Forum Posts: 5
Member Since:
November 1, 2019
sp_UserOfflineSmall Offline
19
June 19, 2020 - 2:14 pm
sp_Permalink sp_Print
0

Lord Dio said

chrissy_spreadsheets said

Lord Dio said

Hi All,

I am fairly comfortable creating my macros and editing them if needs be, however a common issue I’m having is that when I decide to add new columns into my excel sheet for whatever reason, the macro doesn’t account for this and it runs its filters in the same column it did previously without shifting across. 

Is there a way to make my macros sticky to the columns I’ve applied them to so that this problem is eliminated? 

Thanks in advance!

  

Dio – I’m currently looking for topics for the next live series, we can cover this.  You could email me an example file if you like – info at tigersolutions dot co dot uk.

The football course is on the backburner until the end of lockdown.  I would say the 30 for 30 beginner course would be really useful even for those looking specifically at football data.

  

Thanks Chris. It’s far too large/messy/complicated a sheet to send over unfortunately. 

  

I have no doubt – but can you put together a little example file that illustrates the problem?

Forum Timezone: Europe/London
Most Users Ever Online: 92
Currently Online: penang65, swanst1991, john58, DarrenP66, pwd1961
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Lord Dio: 125
leewillo75: 102
@capitalchrisw: 100
Neil: 77
des911: 74
Callum: 68
jamiep: 60
ikeee64: 55
gurds1991: 53
jb8426: 52
Newest Members:
lewismcd
foiebqkf
davidssasw
parimatch
oknafox
dazza185
redrory104
dazzadwm
dgilly8
jasoncuh
Forum Stats:
Groups: 5
Forums: 24
Topics: 357
Posts: 2634

 

Member Stats:
Guest Posters: 3
Members: 5076
Moderators: 2
Admins: 2
Administrators: Ian, Geeky Boy
Moderators: ArtTest, davefts