Help with VBA code!

Wikell · Jun 2, 2011

Hi,

I really need help finding a code (VBA) in Excel that solves probelm below (I know how to fix it with an array formula direct in Excel, so I'm looking for a VBA-code). My example: I have values in 2 columns ("AAAA" & "BBBB"), and in column "CCCC" I want to get the maximum value from column "BBBB" for all same values in column "AAAA". I have manually filled in values in column "CCCC", these are the values I want the VBA code to filll in for me. Is there anyone who knows how to solve this in VBA?

shg · Jun 2, 2011

How about recording a macro that puts the formula you know in col C, copies, and pastes values over the formulas?

Wikell · Jun 3, 2011

Hi,

Well, the problem is that when I'm using the formula it takes so long time, I have about 500 000 rows that this formula has to go trough, and when I'm using an arrayformula it takes to lomng time. I think it will go faster in VAB-code, is there someone who knows how to solve this?

p45cal · Jun 3, 2011

What is the formula you used?

mirabeau · Jun 3, 2011

would you like to re-explain just how you get column C from cols A and B?

it's not at all obvious to me, and I'd prefer not to start writing VBA when it may turn out to be wasted time by doing the wrong thing

JackDanIce · Jun 3, 2011

This is a brute approach so not as elegant as I'd like and I'm assuming you have headers, but try (I'm going to use columns A, B and C for where you have AAAA BBBB CCCC so adjust afterwards):

Code:

Sub SacreBleu()
 
Dim i As Integer, iMax As Integer
 
Application.ScreenUpdating = False
 
Range("A1:B" & Range("A" & Rows.Count).End(xlUp).row).Sort Key1:=Range("A1"), Order1:=xlAscending, _
    Key2:=Range("B1"), Order2:=xlDescending, Header:=xlYes
iMax = Range("B2")
 
For i = 2 To Range("A" & Rows.Count).End(xlUp).row
    If Range("A" & i) = Range("A" & i + 1) Then
        If iMax >= Range("B" & i) Then Range("C" & i) = iMax
    Else
        Range("C" & i) = iMax
        iMax = Range("B" & i + 1)
    End If
Next i
 
Application.ScreenUpdating = True
 
End Sub

Wikell · Jun 3, 2011

Thanks’ a lot for all replies! The code from JackDanIce worked perfectly well! It feels good to that there are professionals helping us unprofessional....... Thanks’ JackDanIce (and of course everyone else who also responded!).

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

JackDanIce · Jun 3, 2011

You're welcome

Wikell · Jun 3, 2011

Hey JackDanIce,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
I tried to run the VBA in my real Excel-sheet and there I have a lot more columns and rows, so since my experience is limited of course this went wrong..... So maybe you can help me modify (I tried by myself without success...) your VBA-code with facts below:<o

></o

>
<o

></o

>
As you can see from my previous example I had column "AAAA", "BBBB" and "CCCC". I my real Excel sheet column "AAAA" is equal to column "D", column "BBBB" is equal to column "AA" and column "CCCC" is equal to column "AB". And the headlines for columns are presented on row 5 (and then the information per headline starts on row 6). Is it an easy thing to modify your VBA-code that you wrote for me with this new information?<o

></o

>
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20 width=64>AAAA</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 width=64>BBBB</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 width=64>CCCC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20>11111</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>1</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20>22222</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>344</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>344</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20>33333</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>5</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>544</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20 width=64>AAAA</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 width=64>BBBB</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 width=64>CCCC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20>11111</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>1</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20>22222</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>344</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>344</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 15pt; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63 height=20>33333</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>5</TD><TD style="BORDER-BOTTOM-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #d4d0c8; BORDER-RIGHT-COLOR: #d4d0c8; BORDER-LEFT-COLOR: #d4d0c8" class=xl63>544</TD></TR></TBODY></TABLE>

Wikell · Feb 23, 2012

Hi!

Is it possible to help me write a VBA script which calculates how many years, months and days there are between two 2 dates ie in column "A" I have the date of birth and in column "B" I have the date when the person died and the answer should be in column "C" (or if the answer is presented i 3 diffrent columns (C-E) where year, month and days are separated.

Example (also important that this works for people born before 1900-01-01 eg 1832-03-28 etc):

Column A (Born) Column B (Died) Column C (Answer)
1978-03-28 2011-02-02 33 years, X months and Y days

Many thanks!!!!

/Mathias

Help with VBA code!

Wikell

New Member

Excel Facts

shg

MrExcel MVP

Wikell

New Member

p45cal

Well-known Member

mirabeau

Banned user

JackDanIce

Well-known Member

Wikell

New Member

JackDanIce

Well-known Member

Wikell

New Member

Wikell

New Member

Similar threads

Forum statistics

Share this page

Help with VBA code!

New Member

Excel Facts

MrExcel MVP

New Member

Well-known Member

Banned user

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock