Solved: Worksheet change copy to next cell

[Dave T]

Hello All,

I am after a worksheet change macro for a worksheet called ‘Planned Inspections’ that upon entry of data into column A (cell A2 down) will copy the VLOOKUP formula result in column B and paste this in column C as text.

I have another worksheet (called 'GPS Coordinates') in the same workbook with structure numbers in column A and the next column (B) has the longitude and the next column (C) has the latitude.

I am using a CONCATENATE formula in column E to combine these two values with a comma so I can use the final result in Google maps.

Because the Latitude values are preceded by a negative sign, Excel is treating this as a formula unless I have the cell formatted as text.

Hence I am after a worksheet change macro to add to the ‘Planned Inspections’ worksheet that upon entry of a structure number in column A (A2 down) copies the VLOOKUP result from Column B and pastes it as text in column C. I would also like the result in column C to be deleted if the entry in column A is deleted.

Any help would be appreciated.

Regards,
Dave T

[Simon Lloyd]

To integrate in your macro[vba]With Activecell
If .value = ""
.offset(0,2) = ""
else
.offset(0,2) .value= .offset(0,1).value
End If
end with[/vba]

[SamT]

Put this function in "GPS Coordinates' Code Page and use it inplace of your Concatenate
[vba]Function CombineCellsForGoogle(Cell1 As Range, Cell2 As Range) As String
CombineCellsForGoogle = Cell1.Formula & ", " & Cell2.Formula
End Function[/vba] To use it, in E2
[vba]=CombineCellsForGoogle(B2, C2) [/vba] Copy E2 down as needed. You can rename/rewrite it to shorten the name

[SamT]

I am after a worksheet change macro for a worksheet called ‘Planned Inspections’ that upon entry of data into column A (cell A2 down) will copy the VLOOKUP formula result in column B and paste this in column C as text.

I take this means that column C must not contain a formula simply formatted as Text?

[SamT]

Paste this into the Planned Inspections Code Page
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
Dim LastCellInA As Range

'Do nothing if the change is not in Column A
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

LastCellInA = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

For Each Cel In Range("A2", LastCellInA)
'from Simon
With Cel
If .Value = "" Then
.Offset(0, 2) = ""
Else
.Offset(0, 2).Value = CStr(.Offset(0, 1).Value)
End If
End With
Next Cel
End Sub
[/vba]

[Dave T]

Hello Simon and Sam T,

Thanks for the replies,

Sam T, I have tried your suggestion and found the following:

• Had to paste the Function in a module as it returned a #NAME error when placed within the 'GPS Coordinates' worksheet.
• The Function returns the longitude and latitude as a single number, for example -36.75237141.83632

I have been using the following formula to insert a comma and space between the two numbers:

=B2&IF(B2="","",", ")&C2

to return a result like -36.75237, 141.83632 which, when copied as text (no longer a formula) and pasted into Google Maps (one of my favourite places) works well.

I have alse been trying to get Simon's suggestion to work as I need to get the formula or Function copied and pasted as text, but the parts If.value = "" and .offset(0,2) .value= .offset(0,1).value are highlighted in red.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'http://www.vbaexpress.com/forum/showthread.php?t=45622
  With ActiveCell
    If.value = ""
    .Offset(0, 2) = ""
  Else
    .offset(0,2) .value= .offset(0,1).value
  End If
End With
End Sub

Regards,
Dave T

[Dave T]

Hello Sam T,

Looks like we are too quick for each other with our replies.


Your last post looks good but I keep getting an error message that says:

Run-time error '91':
Object variable or With block variable not set

This is happening at the following line:
LastCellInA = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

Do you know why this is occuring ???

Regards,
Dave T

[SamT]

Dave,

Run-time error '91':
Object variable or With block variable not set

My Bad, I forgot to put the KeyWord "Set" in front of LastCellInA.

Ya Gotta "Set" one object to another.

Try This line in the Google Function
[VBA]CombineCellsForGoogle = Cell1.Formula & "", "" & Cell2.Formula[/VBA]

I never seem to get the right number of quotes the first time.

[SamT]

the parts If.value = "" and .offset(0,2) .value= .offset(0,1).value are highlighted in red.

He left the "Then" out of the "If" line.

[Simon Lloyd]

He left the "Then" out of the "If" line.

Ooops my bad, good catch SamT
[VBA]

Option ExplicitPrivate
Sub Worksheet_Change(ByVal Target As Range)
'http://www.vbaexpress.com/forum/showthread.php?t=45622
With ActiveCell
If.value = ""then
.Offset(0, 2) = ""
Else
.offset(0,2) .value = .offset(0,1).value
End If
End With
End Sub

[/VBA]

[Dave T]

Hello Simon & SamT,

Not sure what I am doing wrong so I thought it would just be easier to post the workbook and let you see what is going wrong.

Regards,
Dave T

[SamT]

Dave,

Think I got it now.

Note that I added Sheet4(Sheet4), it's Worksheet_Change procedure is different from the one in Sheet1(Planned Inspections (Macros)) in that it also clears Column "B" when "A" is empty.

All procedures (Macros) and Functions now work as I think you want.

[Dave T]

Hello SamT,

A really appreciate your help.

When I ran your initial Worksheet_Change code and mistakenly entered a number not in the table the VLOOKUP, as expected, returns #N/A and the text value returned was 'Error 2042'.

I used =IF(ISNA(VLOOKUP($A2,'GPS Coordinates'!$A$2:$D$12,4,FALSE)),"",VLOOKUP($A2,'GPS Coordinates'!$A$2:$D$12,4,FALSE)) this also cleared the 'Error 2042' each time.

In your last workbook your code deleted my VLOOKUP formula.
As I did not want to retype the VLOOKUP formula each time I changed the code so that the column B formula is not deleted.

Thanks to both you and Simon I now have two solutions to add a text copy to column C or to remove it when the column A entry is deleted.

Many thanks toyou both.

Regards,
Dave T

[SamT]

I use If ISERROR. I have a worksheet with a few constants, many inputs, which are usually empty, (because I only enter what I need for one formula,) and many formulas that use the constants, the inputs, and the results of other formulas.

Imagine the kinds of errors I can get =0/n; =n/0 = X*#NA; = X/#REF. Et al.