I have this on sheet1 on B2 and B3 , where war-200 is chosen through drop down menu , while another sheet named pics has war-200 on A1 and its picture on b1 , is there a formula to throw the picture on C3 of sheet1 from table of code and pictures
Select Code
WAR - 200
I am doing a vlookup accross two sheets where I am adding up the first Vlookup with the second vlookup (ie vlookup sheet 1 + vlookup sheet2) and in one of the sheets the vlookup formula finds a value but in the other sheet it finds nothing. As such when the two formulas are added together it gives a value of #N/A. Since I have made a macro for this it is annoying.
The macro is as follows:
Sub Vlookup()
Dim rowc As Long
Dim i As Long
Application. ScreenUpdating = False
rowc = Application.WorksheetFunction. CountA( Range("a:a"))
For i = 2 To rowc
Cells(i, 3) = "=-VLOOKUP(RC[-2],Hksaldo!C[-2]:C[3],6,FALSE)+VLOOKUP(RC[-2],Likv!C[-2]:C[2],5,FALSE)"
Next i
End Sub
how I can get around this. Ie when I have a non applicable value i one of the Vlookup formula the formula should only look at the vlookup with a value?
Sheet1 has a number of columns, two of them are 'Name' and 'Signature'.
Sheet2(reference sheet) lists all the names and signatures.
Signatures are all .jpeg files that I have inserted into the relevant cells in sheet2.
I need to do a lookup on sheet2 based on 'Name' and populate the 'Signature' column in sheet1 with the correct signature from sheet2(essentially a vlookup, except that one field is a picture). I discovered that vlookup doesn't work, but I can copy the cell (including the signature). So I run a macro that clears all the pictures(signatures) in sheet1 and then copies the correspnding signatures from sheet2. So first of all, is there an easier way to do this as the macro is taking a long time to run?
Secondly, I also need to filter the rows in sheet1. When I filter the rows, the signatures from rows which are not part of the filtered rows are also displayed. I am guessing this happens because the signatures are not referenced exactly to a particular cell.
I tried to show pictures according to numbers code in my list on the sheet 2, during the xploring of web i saw some examples with vlookup and data validation functions.
View 5 Replies View RelatedI have an workbook with pictures that are linked to external picture files, and they are placed in the workbook via macro. When I email the file to another computer and open it, the pics do not display and show an error:
The linked image cannot be displayed. The file may have been moved, renamed, or deleted, Verify that the link points to the correct file and location.
I would like to be able to save the workbook with the pictures in them (without having to archive them) so that I can open it and see them on another machine.
How can I fill downwards the following formula?
=(SUM(B$2:B13)*0.05)*(J2/12)
=(SUM(B$2:B12)*0.05)*(J3/12)
=(SUM(B$2:B11)*0.05)*(J4/12)
Is there a way to type in a cell and click on a Command Button instead of the Enter Key-- and then run a macro using the value that was typed into that cell? Or any other way to eliminate the need to hit Enter Key to record the user input and then hit Command Button to run the macro? I have 40+ users who login in by selecting their name from a drop down list and then entering their password in another cell. (what they input must match another cell in same row.) Attached is scaled down model of Workbook and the macro that works to login.
View 5 Replies View RelatedI'm trying to manipulate a URL in an already opened IE browser window. In a nutshell, I need to change "http://www.excelforum.com" to "http://www.excelforum.com/this-is-the-new-part"
The code prior to this does the following:
- Opens a new instance of IE
- Enters the username and password required to get into the site
- Scrapes the source code, imports it into Excel, and parses the data
- Finds a row based on the current date and some text
- Extracts from that row a unique identifier...
- Obtains the window handle ID for the instance of IE displaying the website
Now I need to add the unique identifier to the url. I cannot open it in a new tab or instance of the browser, as it revokes back to the login screen. I was assuming that the best way to do this would be to get the window handle, then just send a "tab" to get back to the URL, and enter the new URL. If there's any other way to do this, I'm all ears. Otherwise, how can I send the keys using the window handle?
Would need your expertise to advice on how to create a formula to handle the following data in order to produce the desired output below.
I have some data in Sheet 1 and the corresponding data in Sheet2. The output is in Output Sheet.
If the data in Sheet1, "Student1" match the data in Sheet2, "Student1", then it will copy all the entries for Student1 in other worksheet with the header "Student1" (Output Sheet).
The same thing goes to other Student in the list. For this sample, I only provide a short list of data in Sheet2, it can be long, more than 5 students.
Sheet1
------------
Student1 | Student2 | Student3 | Student4 | Student5
Sheet2
------------
Student2 | Student5 | Student3 | Student4 | Student1
aaa | aaa | fff | aaa | aaa
ddd | fff | ccc | ccc | bbb
eee | ccc | ccc | ddd | ccc
ccc | ddd | ddd | eee | ddd
eee | eee | eee | bbb | eee
bbb | bbb | aaa | fff | fff..................
I can't seem to get the fill handle "+" sign to give the correct amounts when
I want to copy a formula down a column. It seems to skip every 2nd row with
the wrong amount or sometimes it will give a 0.00 sign.
The code handles keydown, keypress events with no problem. Also similar change events, and probably more.
This code is at UF initialize event:
Option Explicit
Dim TextBoxes() As New Class1
Dim ComboBoxes() As New Class1
Private Sub UserForm_Initialize()
Dim TCount As Long, CCount As Long, c As Control
TCount = 0
CCount = 0
'Set TabIndexCollection = New Collection
For Each c In Me.Controls
' TabIndexCollection.Add c, CStr(c.TabIndex)
If TypeOf c Is MSForms.TextBox Then................
How can i use the Autofill handle for letters?
I want to be able to do the following:
In cell A1 i type: A
In cell A2 i type: B
Then i want to use the handle to get in column A the rest like C D E....Z AA AB AC.......AZ BA BB BC etc.
I have below codes:
Private Sub Worksheet_Calculate()
For Each rCell In Range("D2:CU5")
Select Case rCell.Value
Case 1, 0.000000001 To 89.499999999
rCell.Interior.ColorIndex = 3
Case 2, 89.5 To 99.499999999
rCell.Interior.ColorIndex = 6
Case 3, 99.5 To 109.499999999
rCell.Interior.ColorIndex = 4
Case 4, 109.5 To 999
rCell.Interior.ColorIndex = 8
Case Else
rCell.Interior.ColorIndex = 15
End Select
Next rCell
End Sub
However if a value within the range "D2:CU5" happens to be #Div/0! (or any error value for that matter) I get a debug error message. Then the CASE ELSE formatting doesn't apply. I expect that everything else should fall under CASE ELSE. Is there a way to handle this i.e. the formatting under CASE ELSE applies?
How do I get the handle for a combobox on a spreadsheet? I have tried ChildWindowFromPoint and RealChildWindowFromPoint without any luck.
View 9 Replies View RelatedI have a function =TimeSinceIns(3) in cell K3. Where the 3 represents the row number. I would like to fill the function all the way down to cell k100. And be able to do this reguarly. Unfortunately when I use the fill handle it leaves 3 as 3 when I want it to be changing to the appropriate row no. Ie in cell k4 would be =TimeSinceIns(4) and so on.
View 3 Replies View RelatedI have the following formula in a cell C6 : "='Data Dump'!G20". I want the adjacent cell on the right (C7) to be "='Data Dump'!G21" and C8 to be "='Data Dump'!G22" and on and on. I'm trying to use the Auto complete handle on the bottom right hand corner of the cell (which looks like a + when the cursor is over it) to drag the formula so that the "Data Dump - G" values increase as I go (g21, g22, g23 and onwards). However, rather than the numerical part of the cell incrementing, the character (letter) does so instead, so I get H20, I20, J20 and onwards.
View 2 Replies View RelatedI would like to use the Fill Handle to create a column of dates.
The dates must be formatted like this: DD/MM/YYYY HH:MM
The Fill Handle completes the series, but not the way I want.
I want a series that changes the day only. So:
02/02/2012 09:00
03/02/2012 09:00
04/02/2012 09:00
For whatever reason, Excel currently adds a minute or two to the minutes and does not increase the date.
13/2/2012 09:20
14/2/2012 09:20
15/2/2012 09:20
13/2/2012 09:21
[Code] .......
I use a large function when ranking numbers in an array each month. I'm only interested in the top 5 numbers. However, there are occasions when the top 5 numbers contain a tie. How do you build into a large function, logic to handle a tie. Here's my function, which is very simple:
Code:
=LARGE(C$6:C$40,1)
=LARGE(C$6:C$40,2)
ETC.
I am trying to get the caption that is displayed on each window of all the open workbooks. I am using the code below to loop through all the workbook windows, get their handles and finally display their respective captions on a Msgbox.I am having 2 problems:
1- The code loops only once instead of once for each open workbook !
2- The Msgbox displays an empty string instead of the workbook caption !
If I am not mistaken, problem 2 is due to the fact that the Class name"EXCEL7" used in the code refers to a worksheet and not to a workbook. The trouble is I can't figure out what a Workbook Class name is so that I can use it in the FindWindowEx Function
Code:
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long[code]......
The below code looks in a certain column and deletes rows based on values less than the entered value in the input window. The problem with the code below is that it does not remove rows that have negative numbers. There is the possibility of a negative number being in the column so I would like to have that removed as well.
What would the code look like to add to the current code to remove the rows that have negative numbers? -1, -2, etc.
Sub Delete_Part_Input()
Dim Qty As Integer, i As Integer, ans As Variant
Qty = Application.InputBox("What total quantities would you like to delete? Note: This is the value located in the C column.", Type:=1)
If Qty = 0 Then Exit Sub
ans = MsgBox("All totals with a quantity of " & Qty & _
" or less will be deleted." & vbCrLf & vbCrLf & _.............
How can I edit the following code. If the "Dload" tab is not there the code errors out. How can I make it so that if the "Dload" is not there the macro says "Dload tab missing. Do you want to check file and try again" If the user says yes. then the macro lets the user pick another file. If the user chooses No then does similar quesiton like I have here starting with
OpenA.Activate.
ChDrive "E:"
ChDir "E:2007 WorkAnalytical ReviewBalance Sheet"
I have 63,893 rows and 3 columns (Parents, Component and Qty) in a table. This would be typical for a Bill of Materials showing the Qty of Components in a Parent. A lot of Components are common to a mumber of Parents.
In total I have 4,259 Parents and 10,904 Components making up this 63,893 rows in my table.
The problem is that neither the Pivot function in Excel nor Access will handle it, least not the way I'm doing it - the bog standard way ! I just get the "Excel cannot complet this task with available resourses......" message.
Anyone any ideas how to get a Pivot or something resembling one so that I end up with the Components as the Row Header and the Parents as Column Headers.
I know I could go in and manipulate the data but I want to be able to create this Pivot like report every month or so without having to reinvent the wheel each time.
I am trying to get the caption that is displayed on each window of all the open workbooks. I am using the code below to loop through all the workbook windows, get their handles and finally display their respective captions on a Msgbox.
I am having 2 problems:
1- The code loops only once instead of once for each open workbook !
2- The Msgbox displays an empty string instead of the workbook caption !
If I am not mistaken, problem 2 is due to the fact that the Class name"EXCEL7" used in the code refers to a worksheet and not to a workbook. The trouble is I can't figure out what a Workbook Class name is so that I can use it in the FindWindowEx Function
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Sub CommandButton1_Click()
**Dim BookNameLenghth As Long
**Dim strBuffer As String
**Dim bBufferSize As Long
**Dim lParent As Long
**Dim lChild As Long
is it possible to handle errors that do not come from the Excel application from a VBA macro?
I have a macro that calls another application (a custom developed one).
If the user interface of that application is not open, then you get a message like:
REGISTRY ERROR
"Missing the webservice name in the registry database,
If I try to use the classic
On Error Goto ErrorHandler
in this case, it won't work.
When I have a text field, such as 123 Main Street, Suite 456 and I copy it down, I get
123 Main Street, Suite 456
123 Main Street, Suite 457
123 Main Street, Suite 458
What is this feature called, and how do I turn it off?
I would like to create a function where I would be able to ask and answer 20 questions. When using If statement it only allows me to use up to 7 nests.
I have created an example below with all the statements I need using IF but of course this doesn't work so I would need so how to handle this particular issue with so many questions/answers in one. Even though the delta between 5-4 and 3-2 is same I want different answers. I'm using Excel 2010.
=IF(AND(AK58=5;AL58=5);"OK";IF(AND(AK58=5;AL58=4);"1 OP";IF(AND(AK58=5;AL58=3);"2 OP";
IF(AND(AK58=5;AL58=2);"2 OP+1 NV";IF(AND(AK58=5;AL58=1);"2 OP+2 NV";IF(AND(AK58=5;AL58=0);"2 OP+2 NV+INST";
IF(AND(AK58=4;AL58=4);"OK";IF(AND(AK58=4;AL58=3);"1 OP";IF(AND(AK58=4;AL58=2);"1 OP+1
[Code]...
At my new job the CHT/CRT that we receive is three days behind what we are actually using. It makes it hard for me to keep track of my talk time, so I thought I'd use excel to track it. I can see the time I have been on each call through the day. I had to put it in 0:0:0 format to get it to average correctly. (I was trying to get it down to just seconds but that seems a bit complicated. I just then used "average=(a1:a30)" to average them together and it seemed to work a bit.
View 2 Replies View RelatedI have a macro that imports xml and then allows the user to update certain fields. The XML contains many quotation marks and inbound I deal with this by replacing " with ' using Find & Replace.
But before exporting the XML again I need to replace the ' with " but am stuck on how to do this.
For instance I'm trying the following with no success:
Cells.Replace What:="'", Replacement:=""", LookAt:=xlPart" _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
I am trying to copy a countif formula down a column using the fill handle, but when I copy the formatting down the column the range changes. Is there a way to make it so that only the variable changes and the range stays the same?
View 2 Replies View RelatedI have searched the Forum for help on this error 91 but still cannot figure this out.
Below is a format macro I created. I have several ' Find' routines in here and at the beginning of each one I give it an 'On Error GoTo' type statement. This seems to work fine until it gets to a second error. I have moved various 'Find' routines around and it doesn't matter which order they are in, if there are two things it can't find, it gives the error 91 on the second.
Sub Format_SFDC_Detail_Reports()
'
' Macro to format salesforce.com reports with details. rguest
'
On Error Goto errorhandler
Msg = "Do you want to format this report for Landscape (Yes) or Portrait (No)?"
Style = vbYesNoCancel + vbQuestion + vbDefaultButton1 + vbSystemModal
Title = "Format Report as Landscape?"
response = MsgBox(Msg, Style, Title)
If response = vbCancel Then
'Exit the routine
Goto last_line:
End If
If response = vbYes Then
format_style = "Landscape"
End If
If response = vbNo Then
format_style = "Portrait"
End If