Need help to search for a string and overwrite data to same location
up vote
0
down vote
favorite
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 at 1:20
Comintern
18k42354
asked Nov 20 at 17:54
MEC
586
add a comment |
up vote
0
down vote
favorite
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 at 1:20
Comintern
18k42354
asked Nov 20 at 17:54
MEC
586
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something tosourcerng.
– SJR
Nov 20 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 at 19:39
1
Not sure I follow. Is it a case of using.Cells(13, sourcerng.column).Value =...instead?
– SJR
Nov 20 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 at 20:32
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 1:20
Comintern
18k42354
asked Nov 20 at 17:54
MEC
586
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 at 1:20
Comintern
18k42354
asked Nov 20 at 17:54
MEC
586
edited Nov 21 at 1:20
Comintern
18k42354
asked Nov 20 at 17:54
MEC
586
edited Nov 21 at 1:20
Comintern
18k42354
edited Nov 21 at 1:20
Comintern
18k42354
edited Nov 21 at 1:20
Comintern
18k42354
18k42354
asked Nov 20 at 17:54
MEC
586
asked Nov 20 at 17:54
MEC
586
asked Nov 20 at 17:54
MEC
586
586
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something tosourcerng.
– SJR
Nov 20 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 at 19:39
1
Not sure I follow. Is it a case of using.Cells(13, sourcerng.column).Value =...instead?
– SJR
Nov 20 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 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 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something tosourcerng.
– SJR
Nov 20 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 at 19:39
1
Not sure I follow. Is it a case of using.Cells(13, sourcerng.column).Value =...instead?
– SJR
Nov 20 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 at 20:32
1
1
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 at 19:01
Try debugging using F8 key. Step line by line and check where is the problem.
– Pawel Czyz
Nov 20 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something to
sourcerng.– SJR
Nov 20 at 19:09
So if they press yes what should happen exactly? Presumably you need to write something to
sourcerng.– SJR
Nov 20 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 at 19:39
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 at 19:39
1
1
Not sure I follow. Is it a case of using
.Cells(13, sourcerng.column).Value =... instead?– SJR
Nov 20 at 19:54
Not sure I follow. Is it a case of using
.Cells(13, sourcerng.column).Value =... instead?– SJR
Nov 20 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 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 at 20:32
add a comment |
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
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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 at 19:01
So if they press yes what should happen exactly? Presumably you need to write something to
sourcerng.– SJR
Nov 20 at 19:09
@SJR I would execute a bunch of these values .Cells(13, lCol).Value = ws1.Range("D14").Value
– MEC
Nov 20 at 19:39
1
Not sure I follow. Is it a case of using
.Cells(13, sourcerng.column).Value =...instead?– SJR
Nov 20 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 at 20:32