VBA getting more than one value for a range
1
I am creating a code that will look through a full column to make sure there is no cell that already has the same value in column D. My problem is I can't figure out a way to change the range to search through more then 1 cell in this case D5. I tried making a loop but where I am newer to coding I don't know a specific way. Anything that helps is much appreciated.
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
Name = the_sheet.Range("D5")
If Name = Worksheets("Drilling Calculations").Cells(2, 3) Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
vba loops range
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
add a comment |
1
I am creating a code that will look through a full column to make sure there is no cell that already has the same value in column D. My problem is I can't figure out a way to change the range to search through more then 1 cell in this case D5. I tried making a loop but where I am newer to coding I don't know a specific way. Anything that helps is much appreciated.
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
Name = the_sheet.Range("D5")
If Name = Worksheets("Drilling Calculations").Cells(2, 3) Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
vba loops range
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
If you look up a guide on for loops, I think it should be easy. Something like,Name = the_sheet.Range("D1:D50")(or whatever you need). Thenfor each c in Nameadd if statement, and so on. Do you want to compare a list against a list? It's also very easy to highlight duplicates with conditional formatting if you want.
– Christofer Weber
Nov 22 '18 at 16:57
I will look into the for loops, thanks. But I am trying to add a row into a table with data that is collected through a variety of different calculations on a separate page. So its not in a list form, just trying to compare the name entered in the cell to see if its in the system already
– Mitchell Lawlor
Nov 22 '18 at 17:00
If you have an example image of how you want it to work, that would help my tired brain to work it out properly.
– Christofer Weber
Nov 22 '18 at 17:15
So, in column D you have a list of names that you want to check if each has a duplicate where exactly?
– Kubie
Nov 22 '18 at 17:40
1
@Kubie Yes, I thought the same. Couldn't figure out what OP needs till last minute 😉
– JohnyL
Nov 22 '18 at 17:41
add a comment |
1
1
1
I am creating a code that will look through a full column to make sure there is no cell that already has the same value in column D. My problem is I can't figure out a way to change the range to search through more then 1 cell in this case D5. I tried making a loop but where I am newer to coding I don't know a specific way. Anything that helps is much appreciated.
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
Name = the_sheet.Range("D5")
If Name = Worksheets("Drilling Calculations").Cells(2, 3) Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
vba loops range
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
I am creating a code that will look through a full column to make sure there is no cell that already has the same value in column D. My problem is I can't figure out a way to change the range to search through more then 1 cell in this case D5. I tried making a loop but where I am newer to coding I don't know a specific way. Anything that helps is much appreciated.
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
Name = the_sheet.Range("D5")
If Name = Worksheets("Drilling Calculations").Cells(2, 3) Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
vba loops range
vba loops range
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
asked Nov 22 '18 at 16:50
Mitchell LawlorMitchell Lawlor
814
814
If you look up a guide on for loops, I think it should be easy. Something like,Name = the_sheet.Range("D1:D50")(or whatever you need). Thenfor each c in Nameadd if statement, and so on. Do you want to compare a list against a list? It's also very easy to highlight duplicates with conditional formatting if you want.
– Christofer Weber
Nov 22 '18 at 16:57
I will look into the for loops, thanks. But I am trying to add a row into a table with data that is collected through a variety of different calculations on a separate page. So its not in a list form, just trying to compare the name entered in the cell to see if its in the system already
– Mitchell Lawlor
Nov 22 '18 at 17:00
If you have an example image of how you want it to work, that would help my tired brain to work it out properly.
– Christofer Weber
Nov 22 '18 at 17:15
So, in column D you have a list of names that you want to check if each has a duplicate where exactly?
– Kubie
Nov 22 '18 at 17:40
1
@Kubie Yes, I thought the same. Couldn't figure out what OP needs till last minute 😉
– JohnyL
Nov 22 '18 at 17:41
add a comment |
If you look up a guide on for loops, I think it should be easy. Something like,Name = the_sheet.Range("D1:D50")(or whatever you need). Thenfor each c in Nameadd if statement, and so on. Do you want to compare a list against a list? It's also very easy to highlight duplicates with conditional formatting if you want.
– Christofer Weber
Nov 22 '18 at 16:57
I will look into the for loops, thanks. But I am trying to add a row into a table with data that is collected through a variety of different calculations on a separate page. So its not in a list form, just trying to compare the name entered in the cell to see if its in the system already
– Mitchell Lawlor
Nov 22 '18 at 17:00
If you have an example image of how you want it to work, that would help my tired brain to work it out properly.
– Christofer Weber
Nov 22 '18 at 17:15
So, in column D you have a list of names that you want to check if each has a duplicate where exactly?
– Kubie
Nov 22 '18 at 17:40
1
@Kubie Yes, I thought the same. Couldn't figure out what OP needs till last minute 😉
– JohnyL
Nov 22 '18 at 17:41
If you look up a guide on for loops, I think it should be easy. Something like,
Name = the_sheet.Range("D1:D50") (or whatever you need). Then for each c in Name add if statement, and so on. Do you want to compare a list against a list? It's also very easy to highlight duplicates with conditional formatting if you want.– Christofer Weber
Nov 22 '18 at 16:57
If you look up a guide on for loops, I think it should be easy. Something like,
Name = the_sheet.Range("D1:D50") (or whatever you need). Then for each c in Name add if statement, and so on. Do you want to compare a list against a list? It's also very easy to highlight duplicates with conditional formatting if you want.– Christofer Weber
Nov 22 '18 at 16:57
I will look into the for loops, thanks. But I am trying to add a row into a table with data that is collected through a variety of different calculations on a separate page. So its not in a list form, just trying to compare the name entered in the cell to see if its in the system already
– Mitchell Lawlor
Nov 22 '18 at 17:00
I will look into the for loops, thanks. But I am trying to add a row into a table with data that is collected through a variety of different calculations on a separate page. So its not in a list form, just trying to compare the name entered in the cell to see if its in the system already
– Mitchell Lawlor
Nov 22 '18 at 17:00
If you have an example image of how you want it to work, that would help my tired brain to work it out properly.
– Christofer Weber
Nov 22 '18 at 17:15
If you have an example image of how you want it to work, that would help my tired brain to work it out properly.
– Christofer Weber
Nov 22 '18 at 17:15
So, in column D you have a list of names that you want to check if each has a duplicate where exactly?
– Kubie
Nov 22 '18 at 17:40
So, in column D you have a list of names that you want to check if each has a duplicate where exactly?
– Kubie
Nov 22 '18 at 17:40
1
1
@Kubie Yes, I thought the same. Couldn't figure out what OP needs till last minute 😉
– JohnyL
Nov 22 '18 at 17:41
@Kubie Yes, I thought the same. Couldn't figure out what OP needs till last minute 😉
– JohnyL
Nov 22 '18 at 17:41
add a comment |
1 Answer
1
active
oldest
votes
1
Give this a try, let me know if need further help...
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
'Get the last row
Dim lastRow As Long
lastRow = the_sheet.Cells(sht.Rows.Count, "D").End(xlUp).Row
Dim bolCheck As Boolean
Dim R As Long 'row
For R = 1 To lastRow 'Iterate through all rows
If the_sheet.Cells(R, 4) = Worksheets("Drilling Calculations").Cells(2, 3) Then 'If a match found then set to false
bolCheck = True
Exit For 'Match found, exit here...
End If
Next R
'Now we know if there is a duplicate or not
If bolCheck Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
1
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
add a comment |
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%2f53435363%2fvba-getting-more-than-one-value-for-a-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Give this a try, let me know if need further help...
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
'Get the last row
Dim lastRow As Long
lastRow = the_sheet.Cells(sht.Rows.Count, "D").End(xlUp).Row
Dim bolCheck As Boolean
Dim R As Long 'row
For R = 1 To lastRow 'Iterate through all rows
If the_sheet.Cells(R, 4) = Worksheets("Drilling Calculations").Cells(2, 3) Then 'If a match found then set to false
bolCheck = True
Exit For 'Match found, exit here...
End If
Next R
'Now we know if there is a duplicate or not
If bolCheck Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
1
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
add a comment |
1
Give this a try, let me know if need further help...
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
'Get the last row
Dim lastRow As Long
lastRow = the_sheet.Cells(sht.Rows.Count, "D").End(xlUp).Row
Dim bolCheck As Boolean
Dim R As Long 'row
For R = 1 To lastRow 'Iterate through all rows
If the_sheet.Cells(R, 4) = Worksheets("Drilling Calculations").Cells(2, 3) Then 'If a match found then set to false
bolCheck = True
Exit For 'Match found, exit here...
End If
Next R
'Now we know if there is a duplicate or not
If bolCheck Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
1
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
add a comment |
1
1
1
Give this a try, let me know if need further help...
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
'Get the last row
Dim lastRow As Long
lastRow = the_sheet.Cells(sht.Rows.Count, "D").End(xlUp).Row
Dim bolCheck As Boolean
Dim R As Long 'row
For R = 1 To lastRow 'Iterate through all rows
If the_sheet.Cells(R, 4) = Worksheets("Drilling Calculations").Cells(2, 3) Then 'If a match found then set to false
bolCheck = True
Exit For 'Match found, exit here...
End If
Next R
'Now we know if there is a duplicate or not
If bolCheck Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
Give this a try, let me know if need further help...
Sub SaveData()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim Name As String
Set the_sheet = Sheets("Saved Data")
'Get the last row
Dim lastRow As Long
lastRow = the_sheet.Cells(sht.Rows.Count, "D").End(xlUp).Row
Dim bolCheck As Boolean
Dim R As Long 'row
For R = 1 To lastRow 'Iterate through all rows
If the_sheet.Cells(R, 4) = Worksheets("Drilling Calculations").Cells(2, 3) Then 'If a match found then set to false
bolCheck = True
Exit For 'Match found, exit here...
End If
Next R
'Now we know if there is a duplicate or not
If bolCheck Then
MsgBox "Error - Well Name Already Exists. Well Not Saved"
Else
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add
table_object_row.Range(1, 1).Value = Worksheets("Drilling Calculations").Cells(2, 3)
table_object_row.Range(1, 2).Value = Worksheets("Drilling Calculations").Cells(5, 5)
table_object_row.Range(1, 3).Value = Worksheets("Drilling Calculations").Cells(6, 5)
table_object_row.Range(1, 4).Value = Worksheets("Drilling Calculations").Cells(7, 5)
table_object_row.Range(1, 5).Value = Worksheets("Drilling Calculations").Cells(8, 5)
table_object_row.Range(1, 6).Value = Worksheets("Drilling Calculations").Cells(5, 17)
table_object_row.Range(1, 7).Value = Worksheets("Drilling Calculations").Cells(6, 17)
table_object_row.Range(1, 8).Value = Worksheets("Drilling Calculations").Cells(7, 17)
table_object_row.Range(1, 9).Value = Worksheets("Drilling Calculations").Cells(8, 17)
table_object_row.Range(1, 10).Value = Worksheets("Drilling Calculations").Cells(10, 23)
MsgBox "Data Saved"
End If
End Sub
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
answered Nov 22 '18 at 19:46
DarXydeDarXyde
26426
26426
1
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
add a comment |
1
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
1
1
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
The code seems to work properly! Thanks for the help!
– Mitchell Lawlor
Nov 23 '18 at 17:19
add a comment |
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%2f53435363%2fvba-getting-more-than-one-value-for-a-range%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
If you look up a guide on for loops, I think it should be easy. Something like,
Name = the_sheet.Range("D1:D50")(or whatever you need). Thenfor each c in Nameadd if statement, and so on. Do you want to compare a list against a list? It's also very easy to highlight duplicates with conditional formatting if you want.– Christofer Weber
Nov 22 '18 at 16:57
I will look into the for loops, thanks. But I am trying to add a row into a table with data that is collected through a variety of different calculations on a separate page. So its not in a list form, just trying to compare the name entered in the cell to see if its in the system already
– Mitchell Lawlor
Nov 22 '18 at 17:00
If you have an example image of how you want it to work, that would help my tired brain to work it out properly.
– Christofer Weber
Nov 22 '18 at 17:15
So, in column D you have a list of names that you want to check if each has a duplicate where exactly?
– Kubie
Nov 22 '18 at 17:40
1
@Kubie Yes, I thought the same. Couldn't figure out what OP needs till last minute 😉
– JohnyL
Nov 22 '18 at 17:41