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
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?

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.