PDA

View Full Version : using array to add 1 hour to column A



Feebles
06-16-2016, 01:19 AM
Hi , I need to add 1 hour to column A IF cell = "Belmont", "Kalgoorlie","Etc","Etc"
so I think I have to use an Array
Any help will be appreciated
Regards
Graham

offthelip
06-16-2016, 02:02 AM
Assuming the places are in column B and put 01:00:00 in C1 then

=IF(OR(B2="Belmont",B2="Kalgoorlie"),A2+C$1,A2)

will do the job, add extra places in the OR statement as required separted by commas, copy down the column t othe end of the data. You don't need an array formula

snb
06-16-2016, 02:38 AM
=A2+IFERROR(SEARCH(B2," BelmontKalgoorlieetc.")>1,0)

Feebles
06-16-2016, 03:23 AM
Sorry i have not explained my self correctly....My Bad
Column "B" holds data like 2:30 Pm , Column "C" holds data like Belmont and other names
If cells in "B" = "Belmont" add Time(1,00,0) If cells = Morphettville add Time(0,30,0)
now for cells to add 1 hour can be Belmont , Bunbury,geraldton,northam,Pinjarra and a few more

Cells to add 30 min is for where "C" = Balaklava,Morphettville,Gawler,Penola, and others as well

so i was after VBA to change these if possible
Regards
Graham

offthelip
06-16-2016, 03:25 AM
=A2+IFERROR(SEARCH(B2," BelmontKalgoorlieetc.")>1,0)

There is a risk with this solution that a town called "elmont" will incorrectly make a match.

offthelip
06-16-2016, 03:29 AM
Sorry i have not explained my self correctly....My Bad
Column "B" holds data like 2:30 Pm , Column "C" holds data like Belmont and other names
If cells in "B" = "Belmont" add Time(1,00,0) If cells = Morphettville add Time(0,30,0)
now for cells to add 1 hour can be Belmont , Bunbury,geraldton,northam,Pinjarra and a few more

Cells to add 30 min is for where "C" = Balaklava,Morphettville,Gawler,Penola, and others as well

so i was after VBA to change these if possible
Regards
Graham
You state column B holds data like 2:30pm and then later If cells in "B" = "Belmont" , what is your data layout?

Also why do it with vBA when it is easy to do with excel functions?

Feebles
06-16-2016, 03:37 AM
Sorry Typo, my data Headers is like this
Rcdate,Rctime,Track,Rcno, Etc etc
Rctime ('B") col contains Time field
Track ("C") Col contains Text as in "Belmont" etc etc
Sorry about the mistake
Graham

offthelip
06-16-2016, 03:41 AM
My original equation will work if you copy and paste it one column to the right, this will change A to B , B to c and C to D.

why do you want to use VBA?

snb
06-16-2016, 03:56 AM
Sub M_snb()
sn = Columns(2).SpecialCells(2).Resize(, 2)

For j = 2 To UBound(sn)
sn(j, 1) = sn(j, 1) + Abs(InStr("|Belmont|Elmont|Mont|", "|" & sn(j, 2) & "|") > 0) / 24+Abs(InStr("|Paris|Verdun|Bonn|", "|" & sn(j, 2) & "|") > 0) / 48
Next

Columns(2).SpecialCells(2).Resize(, 2) = sn
End Sub

SamT
06-16-2016, 05:20 AM
Do you want to add the extra time to B?
OR
Do yo want to add B and the Extra time together in D?

Start with
12:30 PM Belmont
Result
1:30PM Belmont
OR
12:30PM Belmont 1:30PM

Feebles
06-16-2016, 10:41 AM
SNB: Thanks that works perfectly and i must say I have never seen code like that before.
I tested it out and works nicely
Thanks very Much
I apologize for not describing correctly my aims in the first place, I will be more dilligent in future.
Thanks to other members for helping out
Graham

snb
06-16-2016, 01:35 PM
It has a very simple structur:

1. read information form the workbook
2. do calculations/modifications of that information
3. write the result into the workbook

And: do as much as possible in memory.