VBAで値だけコピー

質問

EXCEL VBAで別ファイルの値をコピーして貼り付けをしているのですが、セルの中の一部のみを貼り付けしたいと思いますが、
どのようにしたらいいのでしょうか？

関数では取り出すことができたのですが、できればVBAの中で処理できればと思っています。
=IFERROR(MID(Z2,FIND("6",Z2),FIND(" has",Z2)-FIND("6",Z2)),"")

やりたいこと
１．左から見て6～空白までの値のみを貼り付けしたい
２．エラーの時は空白にしたい

Sub SO_Copy()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim FilePath, FileName As String

'C8&C9にファイルパス/ファイル名を入力
FilePath = ws.Range("C8").Value & "\" & ws.Range("C9").Value
FileName = ws.Range("C9").Value

Workbooks.Open FilePath
'Z2～コピーして、"メール送信.xlsm"のD11以降に値だけ貼り付け
Range("Z2", Range("Z2").End(xlDown)).Copy

Windows("メール送信.xlsm").Activate
Range("D11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Workbooks(FileName).Close
End Sub

回答No.4

HohoPapa · ベストアンサー

>左から見て6～空白までの値のみを貼り付けしたい
空白がない、6がない場合は空白にするということでいいんでしょうか？

例えば　
ABC DEF 1236789012XYZ 678
の場合は、
6789012XYZ
となればいいでしょうか？

ぶっちゃけ、泥臭くソースコードを書く必要があるものと思います。
以下にSampleコードを提示しますので、よかったら使ってみてください。
なお、詳しくはコードを読んでください。

また、
期待と異なるようなら指摘してください。
修正したコードをポストできると思います。

Sub SO_Copy()

　Dim FromBook As Workbook
　Dim ToBook As Workbook
　Dim FromSheet As Worksheet
　Dim ToSheet As Worksheet
　Dim FilePath, FileName As String
　Dim LastRow As Long '複写元最終行番号
　Dim i As Long

　Set ToBook = ThisWorkbook
　Set ToSheet = ToBook.Sheets("Sheet1")
　FilePath = ToSheet.Range("C8").Value & "\" & ToSheet.Range("C9").Value
　FileName = ToSheet.Range("C9").Value

　Set FromBook = Workbooks.Open(FilePath)
　Set FromSheet = FromBook.Sheets("Sheet1")
　'Ｚ列末尾行番号を取得
　LastRow = FromSheet.Cells(Rows.Count, 26).End(xlUp).Row

　For i = 2 To LastRow
　　ToSheet.Cells(i + 9, 4).Value = orgPicup(FromSheet.Cells(i, 26).Value)
　Next

　ToBook.Activate
　ToSheet.Select
　Workbooks(FileName).Close
End Sub

Function orgPicup(FromData As String) As String

　Dim i As Long
　Dim l As Long
　Dim sw As Boolean
　Dim w As String
　Dim HitFlg As Boolean

　HitFlg = False
　sw = False
　l = Len(FromData)
　w = ""

　For i = 1 To l

　　If ((sw = False) And (Mid(FromData, i, 1) = "6")) Then
　　　sw = True
　　End If

　　If ((sw = True) And (Mid(FromData, i, 1) = " ")) Then
　　　sw = False
　　　HitFlg = True
　　　Exit For
　　End If

　　If sw = True Then
　　　w = w & Mid(FromData, i, 1)
　　End If
　Next i

　If HitFlg = True Then
　　orgPicup = w
　End If

End Function

お礼

HohoPapaさま

ご回答ありがとうございます。
試したところ、思った通りの動きになりました。
大変助かりました。ありがとうございました。

回答No.5

NuboChan

No.3です。

参考までに
https://okwave.jp/qa/q10091725.html

回答No.3

NuboChan

単純にVBAで文字列から数字のみを抽出するではだめなんですか　？

例えば、A列の文字列からB列に数値のみを抜き出す。

|[A] |[B]
[1]|MDT - Standard Order 6209655276 has been saved|6209655276
[2]|Do not |
[3]|MDT - Standard Order 6209655282 has been saved|6209655282
[4]|MDT - Standard Order 6209655284 has been saved|6209655284
[5]|MDT - Standard Order 6209655286 has been saved|6209655286
[6]|test tset |

Option Explicit

'「Microsoft VBScript Regular Expressions 5.5」導入の事

'// 引数1：対象文字列
'// 引数2：検索結果
Sub FindNumberRegExp(s, result)

Dim reg As New RegExp '// 正規表現クラスオブジェクト

'// 検索条件＝数字以外を抽出
'半角数字のみを抽出します。
'[^0-9]を[^0-9０-９]に変更
reg.Pattern = "[^0-9]"

'// 文字列の最後まで検索する
reg.Global = True

'// 指定セルの数字以外の文字を空文字に置き換える
result = reg.Replace(s, "")

End Sub

Sub test()
Dim i As Long
Dim result

For i = 1 To 6
Call FindNumberRegExp(Cells(i, "A"), result)
Cells(i, "B").Value = result
Next

End Sub

お礼

NuboChanさま

ご回答ありがとうございます。
数字だけで取り出すことは思いつきませんでした。
何かの機会に使用させていただければと思います。

ありがとうございました。

回答No.2

kon555

簡単です。
　一旦セルの値を取り込み、文字位置で分解します。
http://officetanaka.net/excel/vba/tips/tips173.htm

　その後に『空白』要はスペースを区切り文字として分割します。
http://officetanaka.net/excel/vba/tips/tips62.htm

　エラーなら、というのがちょっと面倒ですが、文字数のカウントと、空白が含まれるかを判定すれば良いと思います。
（文字数を調べる）
http://officetanaka.net/excel/vba/function/Len.htm

（特定の文字が含まれるか調べる）
https://daitaideit.com/vba-char-instr/

　これらを組み合わせればできます。

お礼

kon555さま

ご回答ありがとうございます。
教えていただきましたリンク先にて勉強いたします。
ありがとうございました。

回答No.1

imogasi

質問の表題と内容がちがうなあ。
x = Range("z2"). Valueでセルの値だけ問題にすることになる。＜ーー値だけコ＜ー
Sub test01()
x = Range("z2").Value
y = Mid(x, InStr(x, "6"), InStr(x, "has") - InStr(x, "6"))　’6以降で、hasが現れる直前文字まで
MsgBox y　’確認用です
End Sub
で要点は、仕舞ではないか？関数と同じことをしているだけ。
私はFind（VBAでは、多セル文章間で使う）を覚える前に、VBAでInstr関数を重宝していた。
実態は、他ブックなどの参照らしいが、質問は論点を単純化して質問せよ。あとは修正応用せよ．長々と質問にコードをコピー貼り付けせず、ポイントを絞る訓練をせよ。それが勉強になると思う。