Re: Change formula to compare first 5 characters
Thanks for the quick reply. It worked perfectly.
Re: Change formula to compare first 5 characters
Thanks for the quick reply. It worked perfectly.
Is it possible to change this formula to only compare the first 5 characters of B2 to the first 5 characters of the cells in row E:E?
=IF(ISERROR(MATCH(B2,'Sheet1'!E:E,0))=FALSE,INDEX('Sheet1'!B:B,MATCH(B2,'Sheet1'!E:E,0)),"")
Thanks,
Re: VBA to click checkbox in Internet Explorer
Sorry no it's an internal site only. Would it be helpful to take a screenshot and paste some of the source code?
Re: VBA to click checkbox in Internet Explorer
Hmmm, thought I replied to this yesterday... Well anyway. thanks, I'll try not to give up and thanks for your help. I added your code and couldn't get a message box to come up, it seems nothing happens.
Re: VBA to click checkbox in Internet Explorer
No luck, I tried many different checkbox names. Starting to wonder if it's possible with the page.
I'm trying to add a check to a checkbox on an intranet page. I've tried using the below code with no luck,
Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Website_Login_Test()
Dim oHTML_Element As IHTMLElement
Dim sURL As String
On Error GoTo Err_Clear
sURL = "http://server/webclient/h3cF1ztjAPkEPD"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.document
'HTMLDoc.all.username.Value = Range("A1")
HTMLDoc.all.username.Value = Sheets("Export").Range("A1")
HTMLDoc.all.password.Value = "password"
For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Application.Wait (Now + TimeValue("0:00:05"))
ie.Visible = True
ie.document.forms(0).all("BATCH-EXPORT").CheckBox = True
' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
Display More
below is the webpage html I'm trying to reference.
<TR style="BACKGROUND-COLOR: #efefef" man_type="Item" man_level="0" man_kids="webclient/h3cF1ztjAPkEPD? TC_file=common/data/wsochildren.xml"
man_tag="h3cF1ztjAPkEPD" man_name="BATCH-EXPORT" man_link="webclient/h3cF1ztjAPkEPD" man_baseclasslist="Item%2CWorkspaceObject"
man_class="Item" man_parent_uid="h3cF1ztjAPkEPD" man_uid="h3cF1ztjAPkEPD" man_object_string="BATCH-EXPORT" man_expanded="true"
man_collapsed="null"><TD style="TEXT-ALIGN: center">
<INPUT type=checkbox value="on"></TD>
Re: Delete entire row when column C is blank
Thanks for the help cytop. It's working now.
Re: Delete entire row when column C is blank
I agree with selecting the entire column, I'm not a programmer I just needed to create something to get a job done so I've been piecing this together from help here and googling the rest
The new issue is, i guess I should have mentioned this piece of code is running in the middle of a bunch of other code. So I assume the exit subs would change to finish the rest of the code?
I would post the code for the entire sheet but people would probably have a heart-attach if they seen it.
Re: Delete entire row when column C is blank
Thanks for the quick reply cytop. I added your additional code and I'm still getting the same error. Do I need to add a Resume Next? Sorry if I didn't understand your response.
I have the following code that deletes a row when the cell in column c is blank. The problem is when there are no blank cells I receive error "Run-time error '1004': Application-defined or object-defined error" the first time I run it. How can I modify the code to continue even if there are no blanks.
thanks
Re: Compare Column on one work sheet to 3 columns on another
So, I was able to do what I'm trying to explain with the code below. It's probably very inefficient so if anybody can fix it I would appreciate it.
'compare columns on hyperlink to drawings sheets
Dim rng1 As Range, rng2 As Range, i1 As Long, szj As Long
For i1 = 1 To Sheets("Drawings").Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Drawings").Range("A" & i1)
For szj = 1 To Sheets("hyperlinks").Range("B" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Hyperlinks").Range("B" & szj)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
rng1.Interior.Color = RGB(255, 255, 0)
End If
Set rng2 = Nothing
Next szj
Set rng1 = Nothing
Next i1
'compare columns on hyperlink to drawings sheets
Dim rng3 As Range, rng4 As Range, i2 As Long, szja As Long
For i2 = 1 To Sheets("Drawings").Range("C" & Rows.Count).End(xlUp).Row
Set rng3 = Sheets("Drawings").Range("C" & i2)
For szja = 1 To Sheets("hyperlinks").Range("B" & Rows.Count).End(xlUp).Row
Set rng4 = Sheets("Hyperlinks").Range("B" & szja)
If StrComp(Trim(rng3.Text), Trim(rng4.Text), vbTextCompare) = 0 Then
rng3.Interior.Color = RGB(255, 255, 0)
End If
Set rng4 = Nothing
Next szja
Set rng3 = Nothing
Next i2
'compare columns on hyperlink to drawings sheets
Dim rng5 As Range, rng6 As Range, i3 As Long, szjb As Long
For i3 = 1 To Sheets("Drawings").Range("B" & Rows.Count).End(xlUp).Row
Set rng5 = Sheets("Drawings").Range("B" & i3)
For szjb = 1 To Sheets("hyperlinks").Range("B" & Rows.Count).End(xlUp).Row
Set rng6 = Sheets("Hyperlinks").Range("B" & szjb)
If StrComp(Trim(rng5.Text), Trim(rng6.Text), vbTextCompare) = 0 Then
rng5.Interior.Color = RGB(255, 255, 0)
End If
Set rng6 = Nothing
Next szjb
Set rng5 = Nothing
Next i3
Display More
Re: Compare Column on one work sheet to 3 columns on another
What I'm trying to do is to compare the values in column B on sheet 3 to the values in Column A on Sheet 2, the values in column B on sheet 2, and the values in column c on sheet 2. So if the values appear in any of the three column change the color of the cell to yellow.
Something like this..
Sheet2
[TABLE="width: 250"]
Column A
[/td][td]Column B
[/td][/tr][tr][td]67867
[/td][td]1234
[/td][/tr][tr][td]0988
[/td][td]345
[/td][/tr][tr][td]789900
[/td][td]567
[/td][/tr]
[/TABLE]
Sheet3
[TABLE="width: 250"]
column A
[/td][td]Column B
[/td][td]Column C
[/td][/tr][tr][td]345
[/td][td]876
[/td][td]1234
[/td][/tr][tr][td]4321
[/td][td]345
[/td][td]987
[/td][/tr][tr][td]567
[/td][td]1234
[/td][td]678
[/td][/tr]
[/TABLE]
Hopefully I was able to explain it better.
Thanks.
I found code that compares column B on sheet3 to column A on sheet 2 and if a match is found it changes the color of the cell on sheet 2 to yellow. I would like to change it to compare column B on sheet3 to column A,B, and C on sheet 2 and change the color to yellow.
This is what I currently have.
Dim rng1 As Range, rng2 As Range, i1 As Long, szj As Long
For i1 = 1 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Sheet2").Range("A" & i1)
For szj = 1 To Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Sheet3").Range("B" & szj)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
rng1.Interior.Color = RGB(255, 255, 0)
End If
Set rng2 = Nothing
Next szj
Set rng1 = Nothing
Next i1
Display More
Thanks,
hello,
I'm trying to find all the cells in a column that have a number somewhere in the value and copy that cells contents to the end of a column on another worksheet.I started with the code below but because some of cells have leading zeros I had to format the column as text, so there are no numeric cells.
Dim lr2 As Long
Dim rCell2 As Range
lr2 = Cells(Rows.Count, 1).End(xlUp).Row
For Each rCell2 In Range("C2:C" & lr2)
If IsNumeric(rCell2) = True Then
rCell2.copy Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
End If
Next rCell2
Display More
For example:
Column A on Sheet1
DCB
5020980
0002710
C343
BC
ES-0655
B94
BA
column A on Sheet2
0002471
006344
4540247
0002539
5020980
0002710
C343
ES-065
B94
Hello,
I was hoping I could get some help with a problem I'm having with removing a character from a number. What I'm trying to do is remove a X if there is one and keep the formatting. The cells look like the list below and I've pasted what I currently have for code to remove the X. But as of right now the cells are getting reformatted and I loss the leading zeros.
Col A
X007555
X010051
X050222
0015454
3584541
'changes cell format to custom text and align cells left
Sheets("Drawings").Select
Range("A:A,B:B,C:C").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Delete all Xs in Column A
Sheets("Drawings").Select
Columns("A:A").Select
Selection.Replace What:="X", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Display More
Thanks,
Re: import folder list but only for file types of pdf
Thanks Mawhrin.
I have the code below I am using to import a list of the contents of a folder but I would like to make a couple changes.
1. I want to only list the files with the extension .pdf
2. I want the imported list to only display the file name up to the first underscore.
Sub List_pdfs()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(Sheets("Export_text_file").Range("D1").Value)
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile
End Sub
Display More
Thanks,