# Pick Data From A Specific Row/column (eg 10/B) Related To Active Cell

Dec 2, 2009

I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)

what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.

if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!

## Pick Up A Specific Number From Column

Jun 9, 2007

I have two columns in excel. Column “A” where I have different group names column "B" all the different numbers and column “C” where I have ranked based on column “A”. I need a formula that would pick a number that is ranked 51 with specific group. Currently I’m using below formula (where MC1 represents one of the groups)that is working great unless there is no tie, however if there is a tie for rank 51 and let say two numbers are 22 and 22 the formula will return 44.

SUMPRODUCT(--(Terr!\$Q\$16:\$Q\$518=MC1),--(Terr!\$AC\$16:\$AC\$518=Main!C69+1),Terr!\$Z\$16:\$Z\$518)

Jul 7, 2014

## Auto Increment Specific Cell Value Based On Different But Related Cell Value

Nov 20, 2011

In my excel workbook, I have a customer table and invoice sheet among many others.

As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.

Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.

For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes

Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:

Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No

## Select Specific Row In Active Column Using VBA

Oct 8, 2013

I need a macro that will select row 15 in the active column. (basically returns to the top of the data where rows 1-14 are frozen in place, only in the current column).

I have been able to accomplish the opposite (skip to specific column while maintaining active row) by using the code below:

Range("V" & (ActiveCell.Row)).Select

But it does not work when I try the reverse:

Range((ActiveCell.Column) & "15").Select

## Paste Formula To Specific Row In Active Column

Jun 12, 2007

I'm trying create a macro to enter a series of forumula's in a series of rows in whatever column is currently selected (or column which has a cell selected). IE if the active cell is C5 I want "=A1+B1" copied to C10 of it was AA43 selected I'd want "=A1+B1" copied to AA10. Have done this with setting a row as a variable, but whenever I've defined the column as one it comes out as a numeric value. and gives me "method range of object global failed"

## VBA - Selecting Active Cell And Working Back To Specific Cell Range?

Jul 2, 2014

I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)

Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select

[Code]...

The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.

## Macro To Extract Data From Specific Column To New Sheets Specific Column?

Jun 14, 2013

I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.

For instance, in Sheet1:

Chicken
Cow
Donkey
Pig

[Code]....

So the above table would be the result i'm aiming for.

NOTE, its not different workbooks. I'm looking for sheet to sheet macro.

I've attached a file as well if someone wants to have a go at it. There are no codes in it.

## Specific Formula Executed In Column Of Data Depending On Selected Value In Cell

Apr 4, 2013

I have a sheet with a country in it in A1 (validation list).

Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)

I taught to do it like this : =IF(\$A\$1="Spain";\$A\$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.

I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..

## Macro-Copy&amp;Paste Fixed Data Ranges Into Last Empty Cell Of Specific Column

Apr 27, 2009

I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.

I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.

(1) Name (2) Address (3) ID# (4) Control#

Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)

Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.

MAIN DATA (columns A-D)
Investor1 (columns E-H)… Investor2 (columns I-L)… Investor9 (columns AK-AN)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9

MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.

The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.

I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.

Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.

I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.

So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.

Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.

Sub transposedata()
Sheets("ConsolidatedYTDReport").Select
Range("E2:H4202").Select
Selection.Copy
XXXXXXXXXXXX
ActiveSheet.Paste
End Sub

## Minus Number From Cell And Result In Specific Cell And Specific Column

Jan 20, 2009

I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)

second question

I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)

## VLOOKUP And SUM Formulas: Pick Up Another Value In Column 9 And Should Add Value In Column 7 And 9 And Return It

Apr 6, 2007

=IF(ISNUMBER(VLOOKUP(\$E8,Su!\$D:\$K,7,FALSE)),VLOOKUP(\$E8,Su!\$D:\$K,7,FALSE),)*\$I8

In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.

## Go To Last Active Cell Column

Apr 7, 2009

I have a macro where I sum a large number of cells in column AZ. How can I have the Macro end in the last cell of column AZ where the sum is located? The length varies in each file.

## If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right

Apr 22, 2009

What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

## How To Translate Number Into Specific Letter Related To Number

Feb 3, 2014

How to translate a specific number into a specific letter related to the number itself.

I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.

So if the letter

B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course

B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course

P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course

P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course

and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.

The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.

and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course

and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.

## Convert 2 Column List To Multiple Related Columns

Nov 28, 2007

I have a long list of 2 columns containing data as follows:

col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3

which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....

## Find SECOND Last Active Cell In Column

Jan 15, 2010

I use the Index/Match formula to find the last active cell in a column quite effectively.

I'm wondering though how to adapt it to find the second last active cell?

EX: Last active cell formula:

Data:
a 10
b 11
c 0
d 12

## Select Top Cell (row 1) In Active Column

Apr 23, 2014

I want a simple macro which will go to the cell in row one in the active cell's column

## Moving Active Cell In Same Row To Column A

Jun 28, 2009

In my macro, I need to, from any column, move the active cell to column A, while remaining in the same row.

## Locate The Last Active Cell In A Column

Dec 2, 2008

Is there a formula I can use to locate the last active cell in a column. I did use the count function, however this isn't always reliable if there are blank cells within the column

## Find Name Of Column Active Cell Is In

Jul 7, 2006

I have a code which looks at sheet2, useing a date value, and returns the Vent value and the Tanker value to sheet1. Because of the file size I have pasted a small section of Sheet2 in the hopes you can see what I am doing. As can be seen my code is not going to work with Offset as soon as the Data fills further down the sheet(every 2 weeks). I need a way to reference the active cells column name (it is a named range) or header name. Both are the same. That is the header name is the same as the name of the range. there is a way to perhaps find the first cell of the column,which I guess would work,but have reached a stale mate with what I've tried so far.

Vent 01Vent 02Vent 03Vent 04
Date3/06/063/06/06n1/06/06
Tanker8248824617606
WhoJ AshJ AshEmptyHarry
Date
Tanker
Who

Private Sub DueCommandButton_Click()
Dim strRecordID As Range
Dim rngData As Range
Dim Vents2 As Range, Tanker_Result As Range
Dim wsheet1 As Worksheet, wsheet2 As Worksheet
Dim Column_Name As Range
Dim ActiveCell As Range
Set wsheet1 = Worksheets("Sheet1")
Set wsheet2 = Worksheets("Sheet2")....................

## Use Active Cell Row To Check Another Column

Dec 22, 2006

I've been racking my brains trying to figure this out but cant, please can someone assist. I need to write some vba code that looks at cells in column aa and ag (on the same row) and displays a message is ag > 0 and aa <> "Agency".

## Sort By Active Cell Column

Jan 7, 2007

I am trying to perform a sort based on the ActiveCell.Column

I thought my code would exclude the hearer rows, but presently it moves the header rows beneath the data

I tried

Same result

What am I doing wrong?
Thanks
-marc

Private Sub comp_mySort()

Selection.Sort Key1:= Cells(1, ActiveCell.Column), _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

## Set Variable To Row & Active Cell Column

Jan 8, 2007

I have a sort procedure I have been working on. Sort By Active Cell Column
Now I would like to make sure the row of the activecell.column is row 7. I tried

Private Sub comp_myMonthlyReport_SortAscend()
Dim rng As Range
With ActiveWindow
rng = .ActiveCell(7, .ActiveCell.Column)
End With
Selection.Sort Key1:=rng, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

But I receive this error: Run-time error '91': Object variable or With block variable not set

## How To Pick Name From Column Based Value Difference In Column B

Dec 12, 2013

I have data like this:

NameRank
pietro30
fabri35
mauri60
andrea45
luigi50
anna90
federica100
julia120

Now I want to pick names from column A with rank difference between name should equal to 30.

Expected result

NameRank
pietro30
mauri60
anna90
julia120

## Copy Cell From Specific Sheet & Paste To Active Sheet

Mar 31, 2008

I would like a macro that will go to a fixed sheet, copy the format, go back to the previous sheet and paste the format. My problems arise going back to the previously activated sheet rather than just a fixed sheet.

## Macro - Moving Active Cell To Column B Of Next Row

Feb 14, 2012

I use this macro to open a hyperlink in "column B" of the next row. However, it only works if I begin the macro from "column N" on the line above. (the hyperlink is always located in column B)

I want to be able to run this macro from any cell on the line above. How to modify it?

Code:
'
'
' Keyboard Shortcut: Ctrl+o
'
[Code]...

HTML Code:

ABCDEFGHIJKLMNO
1ActiveURLWhatDateFirst NameLast NameOtherOther2Other3Street1CityStateZip
An example would be to run it while Cell "I2", "J2", or "K2" is selected and have it open "B3"

## Find Column Based On Active Cell

May 16, 2013

How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.

## Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column

Sep 25, 2009

Is it possible to click on a cell in column C, and have the wishlist below happen:

That active cell's row is hightlighted.

Any cell in that column that has the same value as active cell is also highlighted.

Plus, any cell in another sheet that has that value it's row is highlighted too.

Example:

I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.

## Change Active Cell Value In Hidden Column

Apr 11, 2007

The attached sample workbook has a listbox of areas in a userform. The Textbox on the user form is so that the user can edit the Area names in the listbox. The userform functions fine as long as the column that contains the area name is not hidden. What I want to be able to do is edit the Area Names using the Textbox with the area name column hidden.