Mixing
Excel VBA Userform. Insert the Data in a NOT Empty Row
First, I wish you a happy new Year :)
I have created an user form to fill Data into a Database.
So far so good.
The Following Code Inserts the Data
Private Sub Insert_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 2).Value = Me.DateInput.Value
.Cells(iRow, 4).Value = Me.AssetClass.Value
.Cells(iRow, 5).Value = Me.Topic.Value
.Cells(iRow, 7).Value = Me.BBG.Value
End With
Me.DateInput.Value = ""
Me.AssetClass.Value = ""
Me.Topic.Value = ""
Me.BBG.Value = ""
Unload Me
End Sub
Now it selects the first Row that is completly Empty. The Problem is that some columns like Colum A or C have formulas in them. That's why the user form only fills Data into the 2nd, 4th, 5th and 7th column. That means I need a Code that searches for the first Row in which Column C is empty and inserts the Data in that Row.
I hope you can healp me, and thank you in advance
excel vba userform
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
asked Jan 2 at 17:08
BitBit
294
add a comment |
First, I wish you a happy new Year :)
I have created an user form to fill Data into a Database.
So far so good.
The Following Code Inserts the Data
Private Sub Insert_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 2).Value = Me.DateInput.Value
.Cells(iRow, 4).Value = Me.AssetClass.Value
.Cells(iRow, 5).Value = Me.Topic.Value
.Cells(iRow, 7).Value = Me.BBG.Value
End With
Me.DateInput.Value = ""
Me.AssetClass.Value = ""
Me.Topic.Value = ""
Me.BBG.Value = ""
Unload Me
End Sub
Now it selects the first Row that is completly Empty. The Problem is that some columns like Colum A or C have formulas in them. That's why the user form only fills Data into the 2nd, 4th, 5th and 7th column. That means I need a Code that searches for the first Row in which Column C is empty and inserts the Data in that Row.
I hope you can healp me, and thank you in advance
excel vba userform
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
asked Jan 2 at 17:08
BitBit
294
1
Something likeMsgBox Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Rowto find the last row in column C OR in your code changews.Cells.Findtows.Columns(3).Find. Keep in mind thatFINDwill return Nothing if column 3 is blank and Nothing.Row+1 is an error.
– Darren Bartrup-Cook
Jan 2 at 17:27
add a comment |
First, I wish you a happy new Year :)
I have created an user form to fill Data into a Database.
So far so good.
The Following Code Inserts the Data
Private Sub Insert_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 2).Value = Me.DateInput.Value
.Cells(iRow, 4).Value = Me.AssetClass.Value
.Cells(iRow, 5).Value = Me.Topic.Value
.Cells(iRow, 7).Value = Me.BBG.Value
End With
Me.DateInput.Value = ""
Me.AssetClass.Value = ""
Me.Topic.Value = ""
Me.BBG.Value = ""
Unload Me
End Sub
Now it selects the first Row that is completly Empty. The Problem is that some columns like Colum A or C have formulas in them. That's why the user form only fills Data into the 2nd, 4th, 5th and 7th column. That means I need a Code that searches for the first Row in which Column C is empty and inserts the Data in that Row.
I hope you can healp me, and thank you in advance
excel vba userform
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
asked Jan 2 at 17:08
BitBit
294
First, I wish you a happy new Year :)
I have created an user form to fill Data into a Database.
So far so good.
The Following Code Inserts the Data
Private Sub Insert_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 2).Value = Me.DateInput.Value
.Cells(iRow, 4).Value = Me.AssetClass.Value
.Cells(iRow, 5).Value = Me.Topic.Value
.Cells(iRow, 7).Value = Me.BBG.Value
End With
Me.DateInput.Value = ""
Me.AssetClass.Value = ""
Me.Topic.Value = ""
Me.BBG.Value = ""
Unload Me
End Sub
Now it selects the first Row that is completly Empty. The Problem is that some columns like Colum A or C have formulas in them. That's why the user form only fills Data into the 2nd, 4th, 5th and 7th column. That means I need a Code that searches for the first Row in which Column C is empty and inserts the Data in that Row.
I hope you can healp me, and thank you in advance
excel vba userform
excel vba userform
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
asked Jan 2 at 17:08
BitBit
294
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
asked Jan 2 at 17:08
BitBit
294
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
edited Jan 2 at 17:22
Ryan Wildry
3,5611926
3,5611926
asked Jan 2 at 17:08
BitBit
294
asked Jan 2 at 17:08
BitBit
294
asked Jan 2 at 17:08
BitBit
294
294
1
Something likeMsgBox Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Rowto find the last row in column C OR in your code changews.Cells.Findtows.Columns(3).Find. Keep in mind thatFINDwill return Nothing if column 3 is blank and Nothing.Row+1 is an error.
– Darren Bartrup-Cook
Jan 2 at 17:27
add a comment |
1
Something likeMsgBox Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Rowto find the last row in column C OR in your code changews.Cells.Findtows.Columns(3).Find. Keep in mind thatFINDwill return Nothing if column 3 is blank and Nothing.Row+1 is an error.
– Darren Bartrup-Cook
Jan 2 at 17:27
1
1
Something like
MsgBox Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Row to find the last row in column C OR in your code change ws.Cells.Find to ws.Columns(3).Find. Keep in mind that FIND will return Nothing if column 3 is blank and Nothing.Row+1 is an error.– Darren Bartrup-Cook
Jan 2 at 17:27
Something like
MsgBox Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Row to find the last row in column C OR in your code change ws.Cells.Find to ws.Columns(3).Find. Keep in mind that FIND will return Nothing if column 3 is blank and Nothing.Row+1 is an error.– Darren Bartrup-Cook
Jan 2 at 17:27
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
});
}
});
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%2f54010407%2fexcel-vba-userform-insert-the-data-in-a-not-empty-row%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
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.
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%2f54010407%2fexcel-vba-userform-insert-the-data-in-a-not-empty-row%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
Something like
MsgBox Sheet2.Cells(Sheet2.Rows.Count, 3).End(xlUp).Rowto find the last row in column C OR in your code changews.Cells.Findtows.Columns(3).Find. Keep in mind thatFINDwill return Nothing if column 3 is blank and Nothing.Row+1 is an error.– Darren Bartrup-Cook
Jan 2 at 17:27