Mixing
Need help to search for a string and overwrite data to same location
0
I have this code that writes data to the next empty column and it works perfectly when the record does not exist. However, I added code to search for the record (MyVal) and if found to ask if we should overwrite. When it asks the question if the user says no it exits the sub (this is correct behavior) but if the record exists I want it to write the data to the same location. I am pretty sure I have the "Else" in the wrong location and I don't know what code to write to tell it to write the data to the same location as the record found.
Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myVal As String
Dim sourceRng As Range
Dim lCol As Long
MyNote = Sheets("Main").Range("F5").Value & _
" already exists, do you want to overwrite?"
myVal = Sheets("Main").Range("F5").Value ' dropdown list
Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find( _
What:=myVal, LookAt:=xlWhole) 'locate column where to copy from
Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")
If Not sourceRng Is Nothing Then
Answer = MsgBox(MyNote, vbCritical + vbYesNo, "Overwrite??")
If Answer = vbNo Then
Exit Sub
Else
End If
Else
With ws2
Application.EnableEvents = False
lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
.Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
.Range(.Cells(6, lCol), .Cells(8, lCol)).Value =
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
.Range(.Cells(9, lCol), .Cells(10, lCol)).Value =
ws1.Range("G11:G12").Value 'Borrower Name
.Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
.Cells(12, lCol).Value = ws1.Range("D15").Value 'Credit Score
.Cells(13, lCol).Value = ws1.Range("D14").Value 'Rate
.Cells(14, lCol).Value = ws1.Range("C14").Value 'Discount Point
Application.EnableEvents = True
End With
End If
End Sub
excel vba
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
asked Nov 20 '18 at 17:54
MECMEC
697
add a comment |
0
I have this code that writes data to the next empty column and it works perfectly when the record does not exist. However, I added code to search for the record (MyVal) and if found to ask if we should overwrite. When it asks the question if the user says no it exits the sub (this is correct behavior) but if the record exists I want it to write the data to the same location. I am pretty sure I have the "Else" in the wrong location and I don't know what code to write to tell it to write the data to the same location as the record found.
Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myVal As String
Dim sourceRng As Range
Dim lCol As Long
MyNote = Sheets("Main").Range("F5").Value & _
" already exists, do you want to overwrite?"
myVal = Sheets("Main").Range("F5").Value ' dropdown list
Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find( _
What:=myVal, LookAt:=xlWhole) 'locate column where to copy from
Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")
If Not sourceRng Is Nothing Then
Answer = MsgBox(MyNote, vbCritical + vbYesNo, "Overwrite??")
If Answer = vbNo Then
Exit Sub
Else
End If
Else
With ws2
Application.EnableEvents = False
lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
.Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
.Range(.Cells(6, lCol), .Cells(8, lCol)).Value =
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
.Range(.Cells(9, lCol), .Cells(10, lCol)).Value =
ws1.Range("G11:G12").Value 'Borrower Name
.Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
.Cells(12, lCol).Value = ws1.Range("D15").Value 'Credit Score
.Cells(13, lCol).Value = ws1.Range("D14").Value 'Rate
.Cells(14, lCol).Value = ws1.Range("C14").Value 'Discount Point
Application.EnableEvents = True
End With
End If
End Sub
excel vba
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
asked Nov 20 '18 at 17:54
MECMEC
697
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 '18 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something tosourcerng.
– SJR
Nov 20 '18 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 '18 at 19:39
1
Not sure I follow. Is it a case of using.Cells(13, sourcerng.column).Value =...instead?
– SJR
Nov 20 '18 at 19:54
That was the part I didn't know how to do it .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
– MEC
Nov 20 '18 at 20:32
add a comment |
0
0
0
I have this code that writes data to the next empty column and it works perfectly when the record does not exist. However, I added code to search for the record (MyVal) and if found to ask if we should overwrite. When it asks the question if the user says no it exits the sub (this is correct behavior) but if the record exists I want it to write the data to the same location. I am pretty sure I have the "Else" in the wrong location and I don't know what code to write to tell it to write the data to the same location as the record found.
Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myVal As String
Dim sourceRng As Range
Dim lCol As Long
MyNote = Sheets("Main").Range("F5").Value & _
" already exists, do you want to overwrite?"
myVal = Sheets("Main").Range("F5").Value ' dropdown list
Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find( _
What:=myVal, LookAt:=xlWhole) 'locate column where to copy from
Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")
If Not sourceRng Is Nothing Then
Answer = MsgBox(MyNote, vbCritical + vbYesNo, "Overwrite??")
If Answer = vbNo Then
Exit Sub
Else
End If
Else
With ws2
Application.EnableEvents = False
lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
.Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
.Range(.Cells(6, lCol), .Cells(8, lCol)).Value =
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
.Range(.Cells(9, lCol), .Cells(10, lCol)).Value =
ws1.Range("G11:G12").Value 'Borrower Name
.Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
.Cells(12, lCol).Value = ws1.Range("D15").Value 'Credit Score
.Cells(13, lCol).Value = ws1.Range("D14").Value 'Rate
.Cells(14, lCol).Value = ws1.Range("C14").Value 'Discount Point
Application.EnableEvents = True
End With
End If
End Sub
excel vba
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
asked Nov 20 '18 at 17:54
MECMEC
697
I have this code that writes data to the next empty column and it works perfectly when the record does not exist. However, I added code to search for the record (MyVal) and if found to ask if we should overwrite. When it asks the question if the user says no it exits the sub (this is correct behavior) but if the record exists I want it to write the data to the same location. I am pretty sure I have the "Else" in the wrong location and I don't know what code to write to tell it to write the data to the same location as the record found.
Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myVal As String
Dim sourceRng As Range
Dim lCol As Long
MyNote = Sheets("Main").Range("F5").Value & _
" already exists, do you want to overwrite?"
myVal = Sheets("Main").Range("F5").Value ' dropdown list
Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find( _
What:=myVal, LookAt:=xlWhole) 'locate column where to copy from
Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")
If Not sourceRng Is Nothing Then
Answer = MsgBox(MyNote, vbCritical + vbYesNo, "Overwrite??")
If Answer = vbNo Then
Exit Sub
Else
End If
Else
With ws2
Application.EnableEvents = False
lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
.Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
.Range(.Cells(6, lCol), .Cells(8, lCol)).Value =
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
.Range(.Cells(9, lCol), .Cells(10, lCol)).Value =
ws1.Range("G11:G12").Value 'Borrower Name
.Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
.Cells(12, lCol).Value = ws1.Range("D15").Value 'Credit Score
.Cells(13, lCol).Value = ws1.Range("D14").Value 'Rate
.Cells(14, lCol).Value = ws1.Range("C14").Value 'Discount Point
Application.EnableEvents = True
End With
End If
End Sub
excel vba
excel vba
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
asked Nov 20 '18 at 17:54
MECMEC
697
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
asked Nov 20 '18 at 17:54
MECMEC
697
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
edited Nov 21 '18 at 1:20
Comintern
18.5k42454
18.5k42454
asked Nov 20 '18 at 17:54
MECMEC
697
asked Nov 20 '18 at 17:54
MECMEC
697
asked Nov 20 '18 at 17:54
MECMEC
697
697
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 '18 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something tosourcerng.
– SJR
Nov 20 '18 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 '18 at 19:39
1
Not sure I follow. Is it a case of using.Cells(13, sourcerng.column).Value =...instead?
– SJR
Nov 20 '18 at 19:54
That was the part I didn't know how to do it .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
– MEC
Nov 20 '18 at 20:32
add a comment |
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 '18 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something tosourcerng.
– SJR
Nov 20 '18 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 '18 at 19:39
1
Not sure I follow. Is it a case of using.Cells(13, sourcerng.column).Value =...instead?
– SJR
Nov 20 '18 at 19:54
That was the part I didn't know how to do it .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
– MEC
Nov 20 '18 at 20:32
1
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 '18 at 19:01
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 '18 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something to
sourcerng.– SJR
Nov 20 '18 at 19:09
So if they press yes what should happen exactly? Presumably you need to write something to
sourcerng.– SJR
Nov 20 '18 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 '18 at 19:39
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 '18 at 19:39
1
1
Not sure I follow. Is it a case of using
.Cells(13, sourcerng.column).Value =... instead?– SJR
Nov 20 '18 at 19:54
Not sure I follow. Is it a case of using
.Cells(13, sourcerng.column).Value =... instead?– SJR
Nov 20 '18 at 19:54
That was the part I didn't know how to do it .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
– MEC
Nov 20 '18 at 20:32
That was the part I didn't know how to do it .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
– MEC
Nov 20 '18 at 20:32
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398795%2fneed-help-to-search-for-a-string-and-overwrite-data-to-same-location%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398795%2fneed-help-to-search-for-a-string-and-overwrite-data-to-same-location%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 '18 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something to
sourcerng.– SJR
Nov 20 '18 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 '18 at 19:39
1
Not sure I follow. Is it a case of using
.Cells(13, sourcerng.column).Value =...instead?– SJR
Nov 20 '18 at 19:54
That was the part I didn't know how to do it .Cells(5, sourceRng.Column).Value = ws1.Range("F5").Value 'Borrower Name
– MEC
Nov 20 '18 at 20:32