PDA

View Full Version : Solved: Apply Formula in Column B if Column A has Data



enrique63
01-13-2010, 12:28 AM
I have data that automatically goes into Column A. What code can I use to 1) find the first empty cell and 2) apply a formula (i.e. LEFT(A5,3)) in Column B only if there is data in Column A.

This site has been INCREDIBLY helpful. Thank you!

GTO
01-13-2010, 01:23 AM
Greetings,

Try:

Sub exa3()
Dim rngLRow As Range, rngLookIn As Range

'// Change to suit//
Set rngLookIn = Range("A:A")

Set rngLRow = rngLookIn.Find(What:="*", _
After:=rngLookIn(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
'// Bail if empty ///
If rngLRow Is Nothing Then
Exit Sub
Else
'// for the return/value
rngLRow.Offset(, 1).Value = Left(rngLRow.Value, 3)
'// or to put in the formula
'rngLRow.Offset(, 1).Formula = "=LEFT(" & rngLRow.Address & ",3)"
End If
End Sub


Hope that helps,

Mark

Bob Phillips
01-13-2010, 04:21 AM
I have data that automatically goes into Column A. What code can I use to 1) find the first empty cell and 2) apply a formula (i.e. LEFT(A5,3)) in Column B only if there is data in Column A.

This site has been INCREDIBLY helpful. Thank you!

If you find the first EMPTY cell in A, how can there be data in A?

enrique63
01-13-2010, 12:40 PM
Thanks GTO, and xld good point. As written, the code above searches Column A to find the last entry, then applies the formula =LEFT($A$8,3) in Column B only for that last entry in Column A. Below is what the result looks like:

Copy/Paste table didn't work, see attached.

How would you modify the code so that the formula would be applied to all the Column B cells that have a value in the Column A cell next to it.

For example, if I remove the red "Fri" above, the cells A5:A8 is new data that when I apply the code, I would like it to apply the formula to all the cells between B5 and B8.

Bob Phillips
01-13-2010, 01:18 PM
Sub exa3()
Dim rngLRow As Range
Dim rngLookIn As Range

'// Change to suit//
Set rngLookIn = Range("A:A")
Debug.Print "rngLRow"
Set rngLRow = rngLookIn.Find(What:="*", _
After:=rngLookIn(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)

'// Bail if empty ///
If rngLRow Is Nothing Then
Exit Sub
Else
Range("B2", rngLRow.Offset(0, 1)).Formula = "=IF(A2<>"""",LEFT(A2,3),"""")"
End If
End Sub

GTO
01-13-2010, 01:18 PM
Hi Enrique,


...As written, the code above searches Column A to find the last entry, then applies the formula =LEFT($A$8,3) in Column B only for that last entry in Column A...

How would you modify the code so that the formula would be applied to all the Column B cells that have a value in the Column A cell next to it...

Try:

Option Explicit

Sub exa3()
Dim rngCell As Range, rngData As Range

'// Change to suit//
Set rngData = Range("A:A")

Set rngCell = rngData.Find(What:="*", _
After:=rngData(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
'// Bail if empty ///
If rngCell Is Nothing Then
Exit Sub
Else

Set rngData = Range(rngData(1), rngCell)

For Each rngCell In rngData
If Not rngCell.Value = vbNullString Then
'// or to put in the formula
rngCell.Offset(, 1).Formula = "=LEFT(" & rngCell.Address & ",3)"
End If
Next
End If
End Sub

Hope that helps,

Mark

enrique63
01-13-2010, 03:15 PM
Thanks Mark and xld. Both codes worked, although Mark, your code applies the formula over my header in B2.

This is less important, but how would you change the code so that when I click on any cell in Column B, the formula bar would display the value and not the formula?

Also, what needs to be changed so that this code is applied every time I open the workbook without having to click a macro button?

GTO
01-14-2010, 01:21 AM
Hi Enrique,


...although Mark, your code applies the formula over my header in B2.

Sorry, that was less than stellar on my part. To fix, you could either change:

Set rngData = Range("A:A")
'TO
Set rngData = Range("A2:A" & Rows.Count)

Or, you could change:

Set rngData = Range(rngData(1), rngCell)
'TO
Set rngData = Range(rngData(2), rngCell)


Please note that I am not suggesting either, just showing a couple of ways of fixing my blunder in missing the header...

I would use Bob's and skip the looping, excepting I have a question reference:


This is less important, but how would you change the code so that when I click on any cell in Column B, the formula bar would display the value and not the formula?

Also, what needs to be changed so that this code is applied every time I open the workbook without having to click a macro button?

I am unaware of any way to display the value (vs formula) in the formula bar, save clicking in the formula bar and pressing F9.

That said, I guess I am still a bit curious as to why we want the formula in there anyways? Given that we want to run the code automatically, why not just have the values returned to the cells.

You could use the worksheet's change event to "sense" when a value or values change in Col A, and update the adjacent cells in Col B.

If I have my tests correct, something like:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A:A").Resize(Range("A:A").Rows.Count - 1).Offset(1)) Is Nothing _
And (Application.Intersect(Target, Range(Cells(1, 2), Cells(Rows.Count, Columns.Count))) Is Nothing _
And Application.Intersect(Target, Range("A1")) Is Nothing) Then

Application.EnableEvents = False
Target.Offset(, 1).Formula = "=LEFT(" & Target.Address & ",3)"
Target.Offset(, 1).Value = Target.Offset(, 1).Value
Application.EnableEvents = True
End If
End Sub

I think this should put the formula in however much gets pasted at a time, and overwrite the cells w/the val(s).

Hope that helps,

Mark

enrique63
01-14-2010, 12:46 PM
THANK YOU Mark! Your last suggestion is working very well. Returning the values to the cells is exactly what I wanted, so only the value and not the formula is in the cell.

I've changed my imported data from Column A to C, but replacing the A's with C's (as shown below) is not working, and I'm not understanding the code enough to make it work. What needs to also be changed?


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("C:C").Resize(Range("C:C").Rows.Count - 1).Offset(1)) Is Nothing _
And (Application.Intersect(Target, Range(Cells(1, 2), Cells(Rows.Count, Columns.Count))) Is Nothing _
And Application.Intersect(Target, Range("C1")) Is Nothing) Then

Application.EnableEvents = False
Target.Offset(, 1).Formula = "=LEFT(" & Target.Address & ",3)"
Target.Offset(, 1).Value = Target.Offset(, 1).Value
Application.EnableEvents = True
End If
End Sub

GTO
01-14-2010, 02:21 PM
Off to work for this lad, but I'll try and look this evening.

In the meantime, I would like to be clear on one point: If the values to be manually entered (typed or pasted), are now going ino Col C, where are the returned vals to be placed, in Col D?

In just looking quickly at your changes, I would imagine some goofy stuff may be happening when you enter vals in Col A and maybe B. After you confirm where stuff is going, I'll try and include some commenting to the code changes. That, along with studying a couple of help topics should make it clearer.

Mark

enrique63
01-14-2010, 04:35 PM
HA! I think I got it! The data is going into Column C and the formula returned values into Column E.

I had already figured out that I needed to change the "1" to "2" in the target.offset.... lines to output the returned values 2 columns to the right instead of just 1 column.

Then, I finally figured out that the "1,2" needed to be changed to "3,5" for Column 3 and 5 (or Column C & Column E) in the line below:
Application.Intersect(Target, Range(Cells(3, 5), Cells(Rows.Count, Columns.Count

I don't know that I'll ever become a VBA Master, but thanks to people like you, I learn something new every day. Thank you!!!

GTO
01-14-2010, 10:09 PM
HA! I think I got it! The data is going into Column C and the formula returned values into Column E.

Mostly, and nice efforts :)


I had already figured out that I needed to change the "1" to "2" in the target.offset.... lines to output the returned values 2 columns to the right instead of just 1 column.

:thumb


Then, I finally figured out that the "1,2" needed to be changed to "3,5" for Column 3 and 5 (or Column C & Column E) in the line below:
Application.Intersect(Target, Range(Cells(3, 5), Cells(Rows.Count, Columns.Count

Oopsie...

The arguments for Cells are Cells(Row, Column), so the range referenced is E3:IV65536. You can test like:
MsgBox Range(Cells(3, 5), Cells(Rows.Count, Columns.Count)).Address

Referring to the code before your last changes, what it essentially did was test to ensure that the cell(s) being changed (ie-Target) were in Col A, or more accurately, that at least one cell being change intersected Col A.

Thus, if you selected let's say, A5:C10 and pressed the delete key, this test would pass. But since this means that B5:C10 would be included in the changing cells, we wouldn't want the remaining code executed, so the additional tests were to make sure that no cell inclkuded in Target were in cell A1, Cell B1, or any cells in Columns C and onward.

That said, while my tests would work fine, the first bit (testing to make sure cells in A2:A65536 are changing) becomes unnecessary, as the remaining tests (checking to make sure no other cells are changing) effectively negate the need for the first test...

Now, since we are moving things over a bit, what I believe would be effective is to test to ensure that no cells other than C2:C65536 are changing. Since that means we have to ensure that no cells are changing in more areas, I think that using .Intersect and .Union might do the trick.

See if the below works and the comments seem sensible, and I would suggest reading the vba help topics for Cells, Intersect, and Union.

Option Explicit

Sub fake()
MsgBox Application.Intersect(Range("A1:C10"), Range("B8:C26"), Range("B7:F8")).Address
End Sub

'// Target is/are the cell(s) being changed. So if you had A1:A3 selected and press the//
'// delete button, Target is Range("A1:A3"), and this range gets passed by value to the //
'// change event. //
Private Sub Worksheet_Change(ByVal Target As Range)

'// Now, since we only want the below to execute if changes are made in Col C, we //
'// want to test that Target does NOT include any cell outside of C2:C65536, or more//
'// accurately, C2 thru the last row in C. //
'// //
'// .Intersect will tell us if two or more ranges have a common intersection, such //
'// as A1:A4 and A3:C3 intersect at A3. Likewise, A1:C10, B8:C26, and B7:F8 //
'// intersect in B7:C8. So, if we are checking to see if Target intersects with //
'// more than one other range, we need seperate tests to test Target against ea //
'// range. This is what we essentially did last time, but I included some //
'// unnecessary testing with the several tests. //
'// //
'// With moving the range we want the code to run on from Col A, to another column, //
'// we now have more testing to add, as we want to make sure it doesn't run if cells//
'// in Col A or B, or in cell C1 (your header) or in any column from D onward are //
'// included. //
'// //
'// So we will use Union, which effectively combines or includes two or more //
'// ranges into one. So the below tests to see if any cell in Target is in any of //
'// the cells in the several ranges included in Union. As Intersect will return //
'// the Range of intersecting cells and as we are testing that this return //
'// Is Nothing, then what we are actually doing is ensuring that no Range was //
'// returned by Intersect. As long as no range was returned, we know that the only //
'// changes occurred someplace in C2:C65536 (or the cell in Column C, which is why //
'// we used Rows.Count instead of a hard number), and thus, we want the the vals //
'// entered two columns to the right. //

If Application.Intersect(Target, _
Application.Union(Range("A:B"), _
Range("C1"), _
Range(Cells(1, "D"), _
Cells(Rows.Count, Columns.Count)))) Is Nothing Then

Application.EnableEvents = False
Target.Offset(, 2).Formula = "=LEFT(" & Target.Address & ",3)"
Target.Offset(, 2).Value = Target.Offset(, 2).Value
Application.EnableEvents = True
End If
End Sub



I don't know that I'll ever become a VBA Master, but thanks to people like you, I learn something new every day. Thank you!!!

Shucks, I'm usually pretty happy if the PC doesn't burst into flames when I press F5... You are of course, most welcome. :beerchug:

Mark

Bob Phillips
01-15-2010, 02:38 AM
Then, I finally figured out that the "1,2" needed to be changed to "3,5" for Column 3 and 5 (or Column C & Column E) in the line below:
Application.Intersect(Target, Range(Cells(3, 5), Cells(Rows.Count, Columns.Count

No, Cells(3, 5) refers to cell 3E, not columns C & E.

enrique63
01-15-2010, 04:13 PM
Thanks Mark for the explanation and XLD for the clarification. The code is now making more sense to me.

1) I shifted the target to Column C because Columns A,B,C are being copy/pasted into the spreadsheet with formulas going into Columns E,F,G,H,I. However the most recent code senses the changes in Columns A & B, therefore stops the code before returning the formula value. Is there a way to ignore the changes in Columns A & B?

2) I replaced the LEFT() formula with the one that I really want: =MID($C4,SEARCH("ID:",$C4) + 4,6), but it gives me a syntax error. The formula basically searches for "ID", moves 4 digits to the right, and gives me following 6 digits. I simplified the formula and found that the problem is with the SEARCH part. The code below continues to give me the syntax error. Does the the SEARCH formula not work with VBA?

Target.Offset(, 2).Formula = "=SEARCH("ID"," & Target.Address & ")"
Target.Offset(, 2).Value = Target.Offset(, 2).Value